Filter a Dimension by a measure (that will be aggregated dynamically based on filter context)

[Amir_Meidan]

Hey,

In tableau I can filter dimension (lets say account id) by a measure (lets say revenue).

I Drag the account id column to the filters and choose "filter by condition" - and there i define a measure boolean: sum(Revenue) >30.
it will be evaluated in account level per the level of details in my vizualization and keep only the relevent accounts.

this is happening with no need for the account id to be part of the vizualization and dynamically changes according to level of details in the graphs.
what is the equivalent in power bi for this?
I dont find any way doing it and its very basic and needful

thanks!

[Bibiano_Geraldo]

Hi @Amir_Meidan ,

You can use Visual-Level Filtering.

For example, Create a DAX measure to calculate the condition by this DAX:

Revenue Over 30 = 
IF(
    SUM(Revenue[Amount]) > 30,
    1,
    0
)

Now you can:

• Drag this measure to the visual-level filter pane.
• Set the filter condition to show only values where Revenue Over 30 = 1.

This filter works dynamically and adjusts to the level of detail in the visual.

[Amir_Meidan]

But in my case I want to be applied in account level even when it's not in the visual - for example week in x axis, sum of revenue in y axis- and I want to take into account only the accounts with sum(revenue) where in this particular level of details (week) have more than 30$ revenue. I don't see how you're solution is doing the calculation on account level for each account - returning rows only for accounts with true for the condition. 

[BITomS]

Hi @Amir_Meidan, 

I think you are after a visual filter. In the filter panel on selection of your visual, you can add a measure where you can specify less than etc:

Hope this helps!

[Amir_Meidan]

Don't think it would do the same as tableau does:   if my x axis is week and my data Level of details is day , and I want to sum up only revenue from account id's where in this particular level of details having revenue above 30 - just filtering the table by its original rows won't affect 

[Bibiano_Geraldo · Accepted solution]

Hi @Amir_Meidan

To achieve dynamic filtering at the account level in Power BI (even when Account ID is not in the visual), you can use DAX to create a measure that evaluates the condition at the account level and filters out accounts that don't meet the condition

Try this updated measure:

Filtered Revenue = 
CALCULATE(
    SUM(Revenue[Amount]),
    FILTER(
        VALUES(Revenue[Account ID]), -- Iterate over each account
        CALCULATE(SUM(Revenue[Amount])) > 30 -- Account-level condition
    )
)