Measure as filter?

[joshua1990]

I have a simple table that shows the sales based on 4 tables (master data, sales, budget, calendar).
Now I would like to use a filter at the top of the report with which I can filter whether an article has sales > 0 and whether we also had a budget > 0.
On what basis can I set up these filters?
Unfortunately, measures cannot be set up as a filter.

[Daoud_H · Accepted solution]

Hi @joshua1990 ,

You can create a column wich has the value 'has_sales' and 'has_budget' : 

ColumnFilter = 
IF(
    'Table'[sales] > 0, 
    "has_sales", 
    IF(
        'Table'[budget] > 0, 
        "has_budget", 
        BLANK()
    )
)

From this column you can filter your data.

[MattiaColuccia]

@Greg_Deckler 

Sorry to come back to a closed topic but it would be really helpful to have a general pattern to treat this scenarios. All the solutions I've seen - including this one - do in fact work but they are heavy to implement and scale terribly.

I have a requirement to use some metrics to identify "problematic" customers.

Basically users have defined a few criteria, like "Has a lower margin than the average", "Pays later than the average", "Issues fewer orders than the average", stuff like that. 

This is in my experience a common scenario but if we have to apply the proposed solution, the measures and the model become exponentially complex and the performance deteriorates rapidly with the increase of data volume.

Any suggestion or opinion is greatly appreciated!

[lbendlin]

Have you considered using CoPilot to ask these kinds of questions of your sematic model?

[MattiaColuccia]

This is a good suggestion and I can definitely try but the transformative journey towards Copilot is very much on-going, not all users are allowed/willing/capable/trained/confident to use Copilot yet.

[lbendlin]

Or maybe try the Q&A visual as an intermediate option.

[v-huijiey-msft · Accepted solution]

Hi @joshua1990 ,

What everyone else has said makes a lot of sense.

First, make sure your four tables are properly related to each other.

Second, are your "sales" and "budget" column names?

If so, drag "sales" and "budget" to the Filter at the visual object level and set the condition to is greater than 0.

If you have any other questions please feel free to contact me.

Best Regards,
Yang
Community Support Team

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

[lbendlin]

Unfortunately, measures cannot be set up as a filter.

Measures can be used as "Filter on this visual".

[Daoud_H · Accepted solution]

Hi @joshua1990 ,

You can create a column wich has the value 'has_sales' and 'has_budget' : 

ColumnFilter = 
IF(
    'Table'[sales] > 0, 
    "has_sales", 
    IF(
        'Table'[budget] > 0, 
        "has_budget", 
        BLANK()
    )
)

From this column you can filter your data.

[Greg_Deckler]

@joshua1990 In general, to use a measure in that way, you need to use the Disconnected Table Trick as this article demonstrates: https://community.powerbi.com/t5/Community-Blog/Solving-Attendance-with-the-Disconnected-Table-Trick...