Forum Discussion
Advocate IHow to Logically Compare Table01[CategoryID] with Table02[CategoryID] dynamically
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?
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 thetwo 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 FalseOtherwise 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".
5 Replies
TobiasFHere 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 thetwo 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 FalseOtherwise 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
Microsoft Employee
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
- Greg_Deckler
Community Champion
Have you tried creating a custom column in Item table instead of a measure?
- 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()))
- v-yulgu-msft
Hi TobiasF,
Please show your sample data and expected output so that I can test for you.
Regards,
Yuliana Gu
