topic Re: Comparing two table columns which is having Many to many relationship in DAX Commands and Tips

Comparing two table columns which is having Many to many relationship

Anonymous — Fri, 17 Dec 2021 11:50:22 GMT

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

Check = IF(Out_DataComparison_Totals[IN_Tables]=Out_DataComparison_Totals[EO_IN_Tables]&&Out_DataComparison_Totals[EO_IN_Tables]=Out_DataComparison_Totals[EO_Out_Tables]&&Out_DataComparison_Totals[EO_Out_Tables]=RELATED(vw_IntegrationDataCheck[CMN]),"True","False")

But getting following warning Error

The column 'vw_IntegrationDataCheck[CMN]' either doesn't exist or doesn't have a relationship to any table available in the current context.

any idea . how to resolve this warning Error . thanks in advance.

Re: Comparing two table columns which is having Many to many relationship

VahidDM — Fri, 17 Dec 2021 12:03:26 GMT

Anonymous

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.

https://community.powerbi.com/t5/DAX-Commands-and-Tips/Comparing-two-table-columns-which-is-having-Many-to-many/m-p/2244609#M53826

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/

Re: Comparing two table columns which is having Many to many relationship

smpa01 — Fri, 17 Dec 2021 14:07:04 GMT

Anonymous 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

Re: Comparing two table columns which is having Many to many relationship

smpa01 — Sat, 18 Dec 2021 19:48:22 GMT

Anonymous did you have a chance to look into it?

Re: Comparing two table columns which is having Many to many relationship

Anonymous — Mon, 20 Dec 2021 06:41:44 GMT

smpa01 thank you for you reply .let me check will update you .

Re: Comparing two table columns which is having Many to many relationship

Anonymous — Mon, 20 Dec 2021 08:07:52 GMT

smpa01 Values repated multiple time . i have tired your measure sir

Re: Comparing two table columns which is having Many to many relationship

smpa01 — Mon, 20 Dec 2021 14:16:05 GMT

Anonymous yes that's right mate which is why you need an index column like this