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:08:24 GMT

i have a two table in power bi . i want to compare the values between two tables if values is matched it should return True . if values not matched its should be return false .

My table 1:

SID columntype IN table EO_IN EO_OUT year

241 Adjust 750 750 750 2021

241 Ship 250 450 454 2021

241 Adjust 250 450 454 2022

241 Ship 250 250 250 2022

My table 2 :

columntype year CMN

Adjust 2021 750

Adjust 2022 454

Ship 2021 454

Ship 2022 250

i have a created relationship between two tables

Now table looks like below

SID columntype IN table EO_IN EO_OUT year CMN

241 Adjust 750 750 750 2021 750

241 Ship 250 450 454 2021 454

241 Adjust 250 450 454 2022 454

241 Ship 250 250 250 2022 250

Now i want to create new column in the table . if values matched its return true or if values are not matached return false . look like below

SID columntype IN table EO_IN EO_OUT year CMN Status

241 Adjust 750 750 750 2021 750 True

241 Ship 250 450 454 2021 454 Fasle

241 Adjust 250 450 454 2022 454 Fasle

241 Ship 250 250 250 2022 250 True

tired with realted and tealtedtable() function but getting error . any idea . thanks in advance

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

ValtteriN — Fri, 17 Dec 2021 09:02:49 GMT

Hi,

It would be good to have more information e.g. which column you are using to create relationship and what kind of error you are getting. Regardless I think you can use the following structure:

If(lookupvalue(search column, add two conditions here (4 columns in total))=blank(),False,True)

So the idea here is to check if lookupvalue matches columns in your search table and if it does return true/false.

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

Anonymous — Fri, 17 Dec 2021 10:33:54 GMT

ValtteriN i have used columntype column for relationship between two tables Many to Many Relationship. i am getting below Error

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

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

VahidDM — Fri, 17 Dec 2021 12:00:13 GMT

Hi Anonymous

Try this code to add a new column:

Status = VAR _A = CALCULATE( MAX( 'Table (2)'[CMN] ), FILTER( 'Table (2)', 'Table (2)'[columntype] = EARLIER( 'Table'[columntype] ) && 'Table (2)'[year] = EARLIER( 'Table'[year] ) ) ) RETURN IF( [EO_IN] = _A, TRUE(), FALSE() )

output:

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

Anonymous — Fri, 17 Dec 2021 12:24:09 GMT

VahidDM i am not able to type calculate funcution in new column sir . its getting error

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

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

Anonymous

It works well for me, can you share your PBIX file, I think it's more complicated

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

Anonymous — Fri, 17 Dec 2021 13:36:40 GMT

VahidDM pls find the below link for file .Tap name summary .thank you so much

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

VahidDM — Fri, 17 Dec 2021 12:41:58 GMT

Anonymous

You file has Directquery and I can't see your data and visuals.

Try this as a measure:

Status = VAR _A = CALCULATE( MAX( vw_IntegrationDataCheck [CMN] ), FILTER( vw_IntegrationDataCheck , vw_IntegrationDataCheck[ColumnType] = MAX( Out_DataComparison_Totals[columntype] ) && vw_IntegrationDataCheck[CalendarYear] = MAX( Out_DataComparison_Totals[CalendarYear] ) ) ) RETURN IF( max(Out_DataComparison_Totals[EO_IN_Tables]) = _A, TRUE(), FALSE() )

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

Anonymous — Fri, 17 Dec 2021 12:53:54 GMT

VahidDM i have tired in measure i am able to type calcualte function . but values return only checking column type and Year . i want to check three values also .if three values and scond table CMN values matched should return True sir

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

VahidDM — Fri, 17 Dec 2021 12:57:29 GMT

Anonymous OK try this, and update the column names in the last row of the measure

Status = VAR _A = CALCULATE( MAX( vw_IntegrationDataCheck[CMN] ), FILTER( vw_IntegrationDataCheck, vw_IntegrationDataCheck[ColumnType] = MAX( Out_DataComparison_Totals[columntype] ) && vw_IntegrationDataCheck[CalendarYear] = MAX( Out_DataComparison_Totals[CalendarYear] ) ) ) RETURN IF( MAX( Out_DataComparison_Totals[EO_IN_Tables] ) = _A && MAX( Out_DataComparison_Totals[EO_Out_Tables] ) = _A && MAX( Out_DataComparison_Totals[ IN_Tables] ) = _A, TRUE(), FALSE() )

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

Anonymous — Fri, 17 Dec 2021 13:09:28 GMT

VahidDM sir modifed the measure . true return only for the all four below values same
Eo_IN_tables,Eo_Out_tables,IN_Tables,CMN
please find the below screen shot for your reference . its true or false wrongly returned sir

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

VahidDM — Fri, 17 Dec 2021 13:13:19 GMT

Anonymous what is the aggregation of these Eo_IN_tables,Eo_Out_tables,IN_Tables columns in that Matrix/Table?

and it seems the result should be false in that image your shared?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

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

Anonymous — Fri, 17 Dec 2021 13:22:52 GMT

VahidDM sir i have used iun the table format . its all in don't summarize . True status conditional all the four value should be then only it will be true other wise its false

Example :

Eo_IN_tables Eo_Out_tables IN_Tables CMN Status

22951661 22951661 22951661 22951661 True
22951661 4928060 4776026.39 4776026.39 False