Forum Discussion

KiliMiyamoto's avatar
KiliMiyamoto
Frequent Visitor
2 years ago
Solved

Matrix Comparison DAX Code

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_IDother_table_of_ID
100104
101102
102101
103103
104100
 

 

 
 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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

     

4 Replies

  • littlemojopuppy's avatar
    littlemojopuppy
    Community Champion
    2 years ago

    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's avatar
      KiliMiyamoto
      Frequent Visitor
      2 years ago

      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.

       

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      KiliMiyamoto
      Frequent Visitor
      2 years ago

      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.