topic Re: Measure as filter? in DAX Commands and Tips

Measure as filter?

joshua1990 — Mon, 18 Mar 2024 13:44:29 GMT

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.

Re: Measure as filter?

Greg_Deckler — Mon, 18 Mar 2024 13:51:58 GMT

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/ba-p/279563

Re: Measure as filter?

Daoud_H — Mon, 18 Mar 2024 13:53:24 GMT

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.

Re: Measure as filter?

lbendlin — Mon, 18 Mar 2024 17:00:18 GMT

Unfortunately, measures cannot be set up as a filter.

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

Re: Measure as filter?

Anonymous — Tue, 19 Mar 2024 06:02:11 GMT

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!

Re: Measure as filter?

MattiaColuccia — Mon, 16 Sep 2024 12:23:49 GMT

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!

Re: Measure as filter?

lbendlin — Mon, 16 Sep 2024 12:27:11 GMT

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

Re: Measure as filter?

MattiaColuccia — Mon, 16 Sep 2024 12:32:17 GMT

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.

Re: Measure as filter?

lbendlin — Mon, 16 Sep 2024 12:34:04 GMT

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