I was working with a customer trying to get them to better understand DAX and one if the things that is difficult to understand is how the filtering works in DAX.

The challenge I have found is that when using filters in a DAX measure I cannot visually see what is happening within the DAX Filter.

Below I will show you how I can see the values in the FILTER

Below is the layout of my data model (Always following the best practice princilaps even for a small model)

I have then created the following relationships below between my tables.

The reason for the Cross Filter Direciton set to Both between the Period and Date table has been previously blogged about here: Create Dynamic Periods for Fiscal or Calendar Dates in Power BI

I have created one measure called Sales, which is: Sales = SUM(‘Sales Data'[SalesAmount])

Filtering for Colour = Red

I always like to start with something that is easier to understand. This allows me to be able to make sure I understand that I am doing as well as the concept.

  • The first thing that I did was to create my measure, which would only show the sales where the Product Colour was Red
    • Sales for Colour Red = CALCULATE([Sales],’Products'[Colour] = “Red”)
  • Below is an example of my Products table looks like.
  • I have already created my measure above where I am filtering for the Colour Red
  • The first thing I do is to manually filter the above table in Power BI Desktop which allows me to see the values which will remain when I select the Colour Red from the Products table.
    • Once I filtered the table where Colour = Red I see the following below.
  • I then go back to my Power BI Desktop Report View, where I once again manually create a table to see the total I am expecting to see.
  • I find that using a table makes it a lot easier to work through the values when I can easily see them.
  • As shown below, I have created the following
    • A table with all the sales.
    • A slicer with the colours from the Product table
    • A table with the Sales measure and the colours from the Product table
    • One thing you can see is that the Sales for Colour Red still remains at $298 even though I have not filtered anything.
    • Now when I click on the colour Red my table changes as shown below, which shows me the Sales for the colour Red
    • I can now see that this matches my measure [Sales for Colour Red]
  • My challenge now is that I still cannot see which filter values are being applied as part of the DAX measure.
  • Let me show you how I do that in the following steps below.

Using DAX Studio to see the filter values

I have found that using the great free tool Dax Studio makes the process of viewing the filter values a lot easier to do and I can actually SEE the values.

  • I open Dax Studio and connect to my PBIX
  • What I have learnt is that the DAX function FILTER returns a table.
  • This is awesome, because what I want to see is the values of my filter, which essentially is a table.
  • For me to see the FILTER contents I simply take the Filter part of my DAX measure.
    • Previously this was the syntax for my DAX measure

      Sales for Colour Red = CALCULATE([Sales],’Products'[Colour] = “Red”)

    • The FILTER section is my measure above is the ‘Products'[Colour] = “Red”
  • I then take this and write the following in DAX Studio
    • Here is the actual Syntax
      • EVALUATE
        FILTER ( Products, Products[Colour] = “Red” )
  • I then click Run and I can then see the contents of my filter in the Results as shown below.
    • It is interesting to see that it returns the entire table contents.
    • This also allows me to make sure that my FILTER is returning the values I expect.
  • Now when I compare the results above, to when I manually filtered the Products Table in Power BI Desktop I get the identical values
    • Yes, I know the order is different, but they are the same values.
  • If I wanted to I could add the following to my DAX query in DAX Studio to get the order the same as in my table in Power BI Desktop
    • And the result would be as shown below.

Conclusion

I find this is very useful for when I am working with more complex filters and I am not getting back the expected value.

By using the method above, it allows me to make sure that my filter is filtering as I expect it to.

You can find a version of the PBIX here: See Filter items in DAX CALCULATE Measure.pbix

Thanks for reading and if you got any comments or suggestions I would love to hear them!