Forum Discussion
Apply data to all rows in a filtered column
Hello- fairly new to Power BI so I'm hoping this one is easy. I'm trying to classify each owner as Yes or NO based on whether or not they achieved a Tier 3 (the tiers go from 1-10, and achieving Tier 3 is the minimum threshold I'm seeking). Some owners will never achieve a Tier 3, so their status would be NO.
I've created a conditional formula such that if an owner reaches Tier 3, the Threshold is met as YES. So Dabrowska's Threshold status is YES, and Doghmi's is NO.
I want to understand how all rows in a filtered column can be changed if one defined condition is met. Help!
You have a few different options.
You can join the output to a reference of your original table. Steps would be: 1) finalize your original table, disable load; 2) reference your original from #1 and perform the grouping I outlined above, disable load; 3) reference your original from #1, join the categories from #2, enable load
While this is relatively straightforward to do through the UI, it's not as performant as just doing the work inside the group. For that you'll have to implement with M in your formula bar or advanced editor. Here is the full advanced editor snip:
let Source = Sample, GroupOps = Table.Group(Source, {"Owner"}, {{ "GroupOps", each [ grouped_rows = _, group_category = if List.Max(grouped_rows[Tiers]) >= 3 then "Yes" else "No", grouped_rows_with_category = Table.AddColumn( grouped_rows, "Threshold Met", each group_category, type text ) ][grouped_rows_with_category], type table }}), Combine = Table.Combine( GroupOps[GroupOps] ) in CombineOutput:
9 Replies
So when you say 'to understand how all rows in a filtered column can be changed if one defined condition is met', you mean if one row for an owner meets a condition (Tier ≥ 3), then every row for that owner should change to the same result (YES) and condition applies to the entire group?
You can use a DAX for a new Column
Column =VAR MaxTier =CALCULATE(MAX('Table'[Tier]),ALLEXCEPT('Table', 'Table'[Owner]))RETURNIF(MaxTier >= 3, "YES", "NO")
ALLEXCEPT() evaluates across all rows for that owner.This is GREAT and it works- THANK YOU!!! One more question... how do I get a count of the owners meeting Yes and No status? Ultimately I just want 1 status per owner...
You would need to create DAX measures (not column in table view) for owners who met the threshold like,
Owners (YES) =
CALCULATE(
DISTINCTCOUNT('Table'[Owner]),
'Table'[Tier] >= 3
)Owners (NO) =
DISTINCTCOUNT('Table'[Owner]) - [Owners (YES)]
Please try the following formula:
Threshold Status = VAR MaxTierForOwner = CALCULATE ( MAX ( 'YourTable'[Tiers] ), ALLEXCEPT ( 'YourTable', 'YourTable'[Owner] ) ) RETURN IF ( MaxTierForOwner >= 3, "Yes", "No" )If you are interested in a Power Query solution, the basic operation you are looking for is Group By...
Get the max of Tier to see all Owners who have at least one row of Tier >= 3. You can then add a custom column to do the if [Tier] >= 3 then "Yes" else "No" check or just modify the group by formula to do the same check ( if List.Max([Tier]) >= 3 then "Yes" else "No" )
Here is a quick demo doing the latter.
Using following data:
Sample
Tiers Owner 1 A 2 A 1 A 2 B 3 B 1 B 1 C 1 C 2 C 2 D 1 D 1 D 1 E 3 E Edit: realized your tiers go up to 10, so updated comparison op from = to >=. It's still = in the gif but you get the idea
This is great but how do I get out of the grouping so this shows just as a new column in Power Query and ultimately my table?
You have a few different options.
You can join the output to a reference of your original table. Steps would be: 1) finalize your original table, disable load; 2) reference your original from #1 and perform the grouping I outlined above, disable load; 3) reference your original from #1, join the categories from #2, enable load
While this is relatively straightforward to do through the UI, it's not as performant as just doing the work inside the group. For that you'll have to implement with M in your formula bar or advanced editor. Here is the full advanced editor snip:
let Source = Sample, GroupOps = Table.Group(Source, {"Owner"}, {{ "GroupOps", each [ grouped_rows = _, group_category = if List.Max(grouped_rows[Tiers]) >= 3 then "Yes" else "No", grouped_rows_with_category = Table.AddColumn( grouped_rows, "Threshold Met", each group_category, type text ) ][grouped_rows_with_category], type table }}), Combine = Table.Combine( GroupOps[GroupOps] ) in CombineOutput:
Hi aw3w2 ,
I would take a moment to thank cengizhanarslan , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions
HI aw3w2 ,
I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions.
