Matrix Comparison DAX Code

[KiliMiyamoto]

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

[v-heq-msft · Accepted solution]

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

[KiliMiyamoto]

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. 

[littlemojopuppy]

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.

[KiliMiyamoto]

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.