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.
Hey community,
I'm trying to build a measure for which I don't know if I need it to be in Power Query, Calculated Column or measure.
On a similar table, this is what I'm trying to do
This is the table with the 'many' keys.
Key | Value |
12345 | null |
12345 | SWAP |
54321 | null |
54321 | null |
78945 | null |
78945 | null |
78945 | SWAP |
Once a Key has the value SWAP, the other same Keys need to be considered a SWAP type
I have tried to use the OR feature as a measure, but I get strange results
Can you guys help me out with this?
With Regards,
Y.
Solved! Go to Solution.
@Yahya
Add this as a new column in Data Model:
Swap_Key =
VAR _Adv = CALCULATE(
COUNTROWS('Table (4)'),
'Table (4)'[Value]="SWAP")
RETURN
IF(_Adv>0,"SWAP",BLANK())
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS ? to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Yahya
Add this as a new column in Data Model:
Swap_Key =
VAR _Adv = CALCULATE(
COUNTROWS('Table (4)'),
'Table (4)'[Value]="SWAP")
RETURN
IF(_Adv>0,"SWAP",BLANK())
If you are satisfied with my answer, please mark it as a solution so others can easily find it.
Don't forget to give KUDOS ? to replies that help answer your questions
Subscribe to ExcelFort: Learn Power BI, Power Query and Excel
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
Hey Fowmy,
Thank you for your reply. This seems to work as expected 🙂
@Yahya , what is the output you want
Hey amitchandak,
Thanks for the quick reply
The output I'd like is the following
When the calculation is done, filter out SWAP and distinct count the keys
and I'll make a 2nd measure to filter out the ones that aren't SWAP, in this case Blanks (null), and do the same distinct count of keys.
@Yahya , Try like
calculate(distinctcount(Table[key]),isblank(Table[Value]))
Or
calculate(distinctcount(Table[key]),Table[Value] ="SWAP")
How can I make sure this is correct?
If I do the first, I'll get the blank values, but a key may have 2 switches, either null or "SWAP". The result of my table should be
Key | Value | result |
12345 | null | SWAP |
12345 | SWAP | SWAP |
54321 | null | null |
54321 | null | null |
78945 | null | SWAP |
78945 | null | SWAP |
78945 | SWAP | SWAP |
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 |
---|---|
98 | |
97 | |
81 | |
77 | |
66 |
User | Count |
---|---|
126 | |
105 | |
103 | |
81 | |
72 |