Apply data to all rows in a filtered column

[aw3w2]

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!

[v-sshirivolu]

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

[v-sshirivolu]

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.

[cengizhanarslan]

Please try the following formula:

Threshold Status =
VAR MaxTierForOwner =
    CALCULATE (
        MAX ( 'YourTable'[Tiers] ),
        ALLEXCEPT ( 'YourTable', 'YourTable'[Owner] )
    )
RETURN
IF ( MaxTierForOwner >= 3, "Yes", "No" )

[MarkLaf]

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

[aw3w2]

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?

[MarkLaf · Accepted solution]

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
    Combine

Output:

[MasonMA]

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])

    )

RETURN

IF(MaxTier >= 3, "YES", "NO")

ALLEXCEPT() evaluates across all rows for that owner. 

[aw3w2]

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...

[MasonMA]

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)]