How to Filter Multiple Values in Same Column using DAX

When you working with Tabular modeling projects you may encounter this problem. How we can filter multiple cell values that are in the same column using DAX for creating measure.

Scenario:

Let's say you have a table with Sales data as below. Which contains Product information and sales amounts.


Assume, you have a requirement to create a Calculated Measure to filter only Clothing and Accessories. Of course, you can do this using a filter pane as shown below.

Remember this requirement is not to have multiple predicates with different columns but, in the same column.

Solution:

We can write a DAX code like this to solve this problem. In here I create a Calculated Measure called Other Products which filters all the records in Sales query for Category Accessories and Clothing.

EVALUATE
CALCULATE (
    SUM ( Sales[Sales Amount] ),
    FILTER ( Sales, Sales[Category] IN { "Accessories""Clothing" } )
)


You can achieve the same results like you had earlier through this.



Conclusion:


This is an alternative way of filtering multiple predicate logics in the same column using DAX. Sometimes you may find this as a very useful method especially if you are working with Finance related calculations, Chart of Account. Let us know what you think about this and would like to hear your feedback. 

Comments

Post a Comment