topic Re: Matrix Comparison DAX Code in DAX Commands and Tips

Matrix Comparison DAX Code

KiliMiyamoto — Mon, 11 Dec 2023 23:48:54 GMT

The above capture is a simplified example of the results I want. Currently my DAX code is:

Measure = IF(MAX('some_dataset'[table_of_ID]) == MAX('some_dataset'[other_table_of_ID]), "Y")

It doens't work because of the MAX( ), which ideally I would like to just remove but can't. Does anybody know a work around?

table_of_ID	other_table_of_ID
100	104
101	102
102	101
103	103
104	100

Re: Matrix Comparison DAX Code

littlemojopuppy — Tue, 12 Dec 2023 00:57:34 GMT

Hi KiliMiyamoto

What you're looking for is something like this

IF( ISEMPTY( INTERSECT( VALUES(TableOfID[FieldValue]) VALUES(OtherTableOfID[FieldValue]) ) ), BLANK(), TRUE() )

I typed this in Notepad so I doubt it's syntatically correct but should get you to where you want to be.

Re: Matrix Comparison DAX Code

KiliMiyamoto — Tue, 12 Dec 2023 01:12:27 GMT

Thank you very much for you response. I can see what your code does and I've tried it with my dataset but it returns this:

Which in fact is the exact same results as my original code, which returns:

I can't share the dataset since it belongs to my company.

Re: Matrix Comparison DAX Code

Anonymous — Thu, 14 Dec 2023 01:49:45 GMT

Hi KiliMiyamoto ,

According to your describe, here are my test process

To achieve your goal, you can follow these steps:

Here are my test data

1.Create a calculated table by using DAX

Table 2 = VALUES('Table'[table_of_ID])

2.Create one to one relationship between two tables

3.Create a custom column

Column = VAR T = SELECTEDVALUE('Table'[other_table_of_ID]) var N = SELECTEDVALUE('Table'[table_of_ID]) var res = IF(T = N , "Y",BLANK()) RETURN res

4.Use table 'Table'[other_table_of_ID] as row, 'Table2'[other_table_of_ID] as column, calculated column as value.

5.Final output

In order for you to solve the problem faster, you can refer to the following documentation

How to Get Your Question Answered Quickly - Microsoft Fabric Community

Best Regards,
Albert He

Re: Matrix Comparison DAX Code

KiliMiyamoto — Thu, 14 Dec 2023 20:42:29 GMT

Thank you very much for your response. Unfortunately I could not get your solution to work, not because it's incorrect but because my dataset would not allow me to execute your solution. I have a larger master table, from this, I created the table 2 required. However when I try to create a relationship between the two tables I get a circular dependency error. I tried resolving this by creating a table 3 which contains only the 'other table of ID' (I shouldn't have named the variable as table) but Power BI cannot create table 3 since it exceeds 1,000,000 rows. So yeah sadly I don't think there is anyway for me to get what I want.