How to Logically Compare Table01[CategoryID] with Table02[CategoryID] dynamically

[TobiasF]

Hi,

I'm having a hard time wrapping me head around the following. 

I want to logically compare Table01[CategoryID] with Table02[CategoryID].  

When the data is read and it gets to Table01[CategoryID], (lets say the CategoryID is 1) that needs to be
compared to Table02[CategoryID]. Table02[CategoryID] is indeed 1 so we have a match, otherwise don't match. 

The problem I have is that regardless if I try to use filters, lookupvalue etc. It won't let me do this comparision without

manually enter something specific which is not what I need. 

Basically what I am trying to do is the following

IF( 
      Item[CategoryID] = Activity[CategoryID];
         
                IF( Activity[ActivityType) = "V";
                             
                              DISTINCTCOUNT( Activity[User])
                  )
  )

However IF won't let me compare against column values, for some reason it only wants to compare measures, filters etc just want to have a manually defined value which will not solve my use case.


Any ideas on how to solve this?

[TobiasF · Accepted solution]

Here is how I solved this. 

Problem was because the column values I wanted to compare resided in two different tables. 

factTable[ColumnA]
dimTable[ColumnB]

1. I Used Query Editor to Merge the factTable with the DimTable[ColumnB] I want to compare to. 

(Basically it copied ColumnB from DimTable to factTable. For this to work you need a relationship between the

two tables that work which I had).

Now both of the Columns I want to compare exist in the same table. 

2. I used Query Additor => Add Column => Conditional Colum     on my factTable.

There I configured IF factTable[ColumnA] Equal To  factTable[ColumnB] Set the value of the new conditional column to True

Else if IF factTable[ColumnA] Not Equal To factTable[ColumnB] Set the value of the new conditional column to False

Otherwise set value of new conditional colum to Problem.

This effectivaly creates a TRUE/FALSE flag that I can filter on.

From this I was able to create a Calculated Column based on my needs where I simply added a filter of factTable[ColumnB] = "True".

[v-yulgu-msft]

Hi @TobiasF,

Thanks for your sharing. Would you please accept your shared solution as an answer so that it can benefit more users having similar requirement?

Thanks,
Yuliana Gu

[v-yulgu-msft]

Hi @TobiasF,

Please show your sample data and expected output so that I can test for you.

Regards,
Yuliana Gu

[Greg_Deckler]

Can't be sure of your data, but something like this seemed to work for me:

Column = IF(ISBLANK(LOOKUPVALUE(Activity[CategoryID],Activity[CategoryID],'Item'[CategoryID])),BLANK(),IF(LOOKUPVALUE(Activity[ActivityType],Activity[CategoryID],'Item'[CategoryID])="V",CALCULATE(DISTINCTCOUNT(Activity[User]),RELATEDTABLE(Activity)),BLANK()))

[Greg_Deckler]

Have you tried creating a custom column in Item table instead of a measure?