The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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 |
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
94 | |
94 | |
86 | |
69 | |
65 |
User | Count |
---|---|
232 | |
128 | |
116 | |
82 | |
82 |