Can we compare two tables in a Switch Case in DAX Power BI?

1
votes

Is it possible to include columns from 2 different tables in DAX Switch Function ?

Both of my tables are linked by Many to Many Relationship.
I am trying to create a calculated column in Table A where I want to compare Columns from Table B, but I can't add the Table B column in the DAX Switch formula.
Any suggestions would be appreciated.

Thanks & Regards,
Pratik

Sample
Table B
Table A & Expected Result

2
powerbiswitch-statementdax
What do you mean by compare two table, show some sample table and expected output – Kin Siang
Hi @KinSiang, I added the images to the question( Both Tables are already linked by ID). I am using Switch function in Table A to Compare the values in Table B. If the Values in Table B are 0 then the Comment in Table A for the same ID should be Matched with B. In my case I am just not able to compare the columns between the 2 tables. I hope I was clear, please let me know if you need any other info. – Pratik

2 Answers

1
votes

In my opinion, it doesn't matter whether this table has many relationship, the only thing matter is that you are calculated based on your original table instead of calculate on a new measure, then result is unpredictable:

By create new table from the original table, and add column for the comment, I can provide result as your expectation, hope it solve your problem and accept the answer :)

  1. Create new table from original table (Sheet1 is my old table) and select on ID column

    Table = SELECTCOLUMNS(Sheet1,"ID",Sheet1[ID])

  2. Create new column for comment

    Column = SWITCH(LOOKUPVALUE('Sheet1'[Value],Sheet1[ID],'Table'[ID]), 0,"Matched with A","Matched with B")

The outcome for the new table:

enter image description here

1
votes

Instead of trying to use two columns from two different tables for comparison in DAX, you can use the 'RELATED` DAX function to import a column from Table A to Table B or vice-versa. Then you can use this column for comparison in DAX.

Just set the appropriate relationship between Table A and Table B and create a new column with the RELATED DAX function to import the table that you want to use in the SWITCH function.