Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
i have a two table in power bi . Both tables have a columntype as a common value. its a Many to many relationship.
my dataset looks like below
Out_DataComparison_Totals Table
ColumnType IN_Tables EO_IN_Tables EO_Out_Tables CalendarYear
Adjust 500 400 478 2021
Adjust 1000 1000 1000 2022
Demand 500 1400 478 2021
Demand 2500 2500 2500 2022
vw_IntegrationDataCheck table
ColumnType CalendarYear CMN
Adjust 2021 300
Adjust 2022 1000
Demand 2021 750
Demand 2022 2500
Next after relationship i have CMN value into Out_DataComparison_Totals tables.
Now Out_DataComparison_Totals tables looks like .i want to create extra column for true or fale .
if all values matched column should return True .
if all not values matched column should return fales.
ColumnType IN_Tables EO_IN_Tables EO_Out_Tables CalendarYear CMN Status
Adjust 500 400 478 2021 478 False
Adjust 1000 1000 1000 2022 1000 True
Demand 500 1400 478 2021 750 False
Demand 2500 2500 2500 2022 2500 True
i have created below measure
Solved! Go to Solution.
@THENNA_41 you can write 2 measures like this
CMN =
CALCULATE (
MAX ( vw_IntegrationDataCheck[CMN] ),
TREATAS (
SUMMARIZE (
Out_DataComparison_Totals,
Out_DataComparison_Totals[ColumnType],
Out_DataComparison_Totals[CalendarYear]
),
vw_IntegrationDataCheck[ColumnType],
vw_IntegrationDataCheck[CalendarYear]
)
)
STATUS =
VAR _others =
AVERAGEX (
UNION (
{ MAX ( Out_DataComparison_Totals[EO_IN_Tables] ) },
{ MAX ( Out_DataComparison_Totals[IN_Tables] ) },
{ MAX ( Out_DataComparison_Totals[EO_Out_Tables] ) }
),
[Value]
)
RETURN
IF ( [CMN] = _others, TRUE (), FALSE () )
or a combined measure like this
Combined =
VAR _cmn =
CALCULATE (
MAX ( vw_IntegrationDataCheck[CMN] ),
TREATAS (
SUMMARIZE (
Out_DataComparison_Totals,
Out_DataComparison_Totals[ColumnType],
Out_DataComparison_Totals[CalendarYear]
),
vw_IntegrationDataCheck[ColumnType],
vw_IntegrationDataCheck[CalendarYear]
)
)
VAR _others =
AVERAGEX (
UNION (
{ MAX ( Out_DataComparison_Totals[EO_IN_Tables] ) },
{ MAX ( Out_DataComparison_Totals[IN_Tables] ) },
{ MAX ( Out_DataComparison_Totals[EO_Out_Tables] ) }
),
[Value]
)
RETURN
IF ( _cmn = _others, TRUE (), FALSE () )
The pbix is atatched
@THENNA_41 you can write 2 measures like this
CMN =
CALCULATE (
MAX ( vw_IntegrationDataCheck[CMN] ),
TREATAS (
SUMMARIZE (
Out_DataComparison_Totals,
Out_DataComparison_Totals[ColumnType],
Out_DataComparison_Totals[CalendarYear]
),
vw_IntegrationDataCheck[ColumnType],
vw_IntegrationDataCheck[CalendarYear]
)
)
STATUS =
VAR _others =
AVERAGEX (
UNION (
{ MAX ( Out_DataComparison_Totals[EO_IN_Tables] ) },
{ MAX ( Out_DataComparison_Totals[IN_Tables] ) },
{ MAX ( Out_DataComparison_Totals[EO_Out_Tables] ) }
),
[Value]
)
RETURN
IF ( [CMN] = _others, TRUE (), FALSE () )
or a combined measure like this
Combined =
VAR _cmn =
CALCULATE (
MAX ( vw_IntegrationDataCheck[CMN] ),
TREATAS (
SUMMARIZE (
Out_DataComparison_Totals,
Out_DataComparison_Totals[ColumnType],
Out_DataComparison_Totals[CalendarYear]
),
vw_IntegrationDataCheck[ColumnType],
vw_IntegrationDataCheck[CalendarYear]
)
)
VAR _others =
AVERAGEX (
UNION (
{ MAX ( Out_DataComparison_Totals[EO_IN_Tables] ) },
{ MAX ( Out_DataComparison_Totals[IN_Tables] ) },
{ MAX ( Out_DataComparison_Totals[EO_Out_Tables] ) }
),
[Value]
)
RETURN
IF ( _cmn = _others, TRUE (), FALSE () )
The pbix is atatched
@THENNA_41 did you have a chance to look into it?
@THENNA_41 yes that's right mate which is why you need an index column like this
It seems this post is same as your previous post, I shared a solution in the previous one, please check and let me know if that does not help.
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
47 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
77 | |
50 | |
46 | |
16 | |
12 |