Forum Discussion
Helper IOR feature triggered by key
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.
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
6 Replies
- Fowmy
Super User
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
YahyaHey Fowmy,
Thank you for your reply. This seems to work as expected 🙂
- amitchandak
Yahya , what is the output you want
- Yahya
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.
- amitchandak
Yahya , Try like
calculate(distinctcount(Table[key]),isblank(Table[Value]))Or
calculate(distinctcount(Table[key]),Table[Value] ="SWAP")
