Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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?
Solved! Go to 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".
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".
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
Hi @TobiasF,
Please show your sample data and expected output so that I can test for you.
Regards,
Yuliana Gu
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()))
Have you tried creating a custom column in Item table instead of a measure?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |