DAX to compare columns of differing tables

[DevinB4]

Hello,

I am wanting to compare the count of a column in one table to the value of a column in another table. In the below example, there is a column [Count of SequenceNumber] on 'TableA' and column [COUNT_TOTAL] on 'TableB'. These tables have a relationship on their sequence numbers and the cardinality is many to many.

I want to be able to run an IF statement to check if these values match. The below would be the example of what I would expect:

When I try the example:

MATCH? = IF('TableB'[COUNT_TOTAL] = COUNT(TableA[SequenceNumber]),"YES","NO")

I only receive "NO" in my new [MATCH?] column, which is not what I want. I feel that I am missing some fundamental knowledge of DAX, but I have tried similar solutions using RELATED (given that it is a many to many relationship) and have not had success. Hoping someone can clear up my issue, thanks!

[Anonymous]

Hi @DevinB4 ,

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem.

Best Regards,

Yuna

[Anonymous]

Hi @DevinB4 ,

Based on your description, you can create a calculated column as follows.

my test tables:

Column = IF(ISBLANK(LOOKUPVALUE('B'[COUNT_TOTAL],'B'[COUNT_TOTAL],'A'[Count of Sequence Number])),"no","yes")

Result:

Hope that's what you were looking for.

Best Regards,

Yuna

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Ashish_Mathur]

Hi,

Try to use the LOOKUPVALUE() function.

[DevinB4]

Hi @Ashish_Mathur,

I have attempted LOOKUPVALUE() here in a previous iteration but without success. My problem is when I attempt to compare values from one table to the other, DAX will allow the function to run but will not computer. I have attempted to calculate the difference between a measure of the count of sequence number minus COUNT_TOTAL and got unexpected results. Additionally, trying to run an IF statement will only produce all "YES" or "NO" responses.

The solution here should be simple. I should be able to compare a measure in one table with a column in another but there is some underlying issue.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[DevinB4]

Hey Ashish,

This PBI report utilizes two of my SQL Server connections and I wouldn't be able to share. I appreciate your attempt to help!

Best,

Devin

[amitchandak]

@DevinB4 , Create a common/bridge table sequence number and join to both tables with sequence no of this new table and now using sequence no  of this new table analyze the data together

How to create the bridge table :https://www.youtube.com/watch?v=Bkf35Roman8

[DevinB4]

Hello Amit and thank you for the reply,

I have attempted a bridge connection as given in the video and created two one to many relationships instead of a many to many.

With this change my [COUNT_TOTAL] is unable to correctly calculate itself. This value defaults to "sum" (and summarizes all the rows in that column) and when I try to switch to "don't summarize" the table visual errors out.

I still attempted to run my IF statement shared above after creating the bridge. This produced the same results.

To give more context to where this data comes from, each Power BI table is running a query on a SQL Server table in seperate databases. Hopefully this helps in determing a potential solution.

I am looking into only loading distinct values from the 'TableB' SQL Server to establish a one to many relationship. I will attempt this and report back on my findings.