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")   ...
  • 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

     

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.