I got an interesting question from Vijay asking, “How do we do a slicer with data count?”

This is what Vijay was looking to achieve as shown below and I always enjoy a good challenge.

As I always like to do, is to show how I got to the required result.

  • I currently have got data from my website, which I look at daily to see how my blog posts are tracking.
  • In my dataset I like to see where in the world people are coming from to visit my blog or website.
  • Based on the requirement from Vijay I created a slicer which takes the total sessions per country, which I can then use as a Slicer on my data.

The first thing that I did was to create a calculated table which will have the information I require for the Slicer.

In Power BI Desktop I went to the Modeling Ribbon and clicked on New Table

This will be the Country and Sessions. I created it with the following DAX Syntax below.

Slicer Table =
    ADDCOLUMNS (
        SUMMARIZECOLUMNS ( 'All Web Site Data'[Country] ),
        "Sessionszz", [Sessions]
    )
  • Line 1
    • What I have done above is created a table called Slicer Table
  • Line 2-3
    • This is where I added the ADDCOLUMNS Syntax for my table.
    • NOTE: Thanks to Maxim for commenting and letting me know that I did not need the original CALCULATETABLE
  • Line 4
    • I then used the SUMMARIZECOLUMNS DAX function and put in the ( ‘All Web Site Data'[Country] column, as per my requirement to get a Count of Sessions per Country.
  • Line 5
    • This is where I created my Column Name called “Sessionszz” and my measure called [Sessions]
    • NOTE: When I use measures I only use the Square brackets.
  • Line 6-7
    • I then close off my previous DAX functions.

The result is I now have the following calculated table.

How I had the data in order to create a new calculated column which will have the Country with the Total Sessions. I did it with the following DAX Syntax below.

Country with Sessions =
'Slicer Table'[Country] & " ("
    & 'Slicer Table'[Sessionszz]
    & ")"

 

And the result was I now had my new column in my table showing both the Country and the Total Sessions

Next, I created a relationship from my Fact table ‘All Web Site Data’ to my new calculated table called ‘Slicer Table’ on the Country Column.

I went to the Modeling tab again, and this time clicked on Manage Relationships

I then clicked on New and selected the following as shown below.

I then click Ok and Ok again to come back to Power BI Desktop

Now I was able to create the Slicer, which shows the Country and the Total Sessions.

I have selected the Country “Afghanistan” which has got a total of 4 sessions, and in my table, I can see the sessions and the date when they occurred.

Each time the dataset is refreshed the calculated table will also be refreshed which will ensure that the slicer values are up to date.

This was a great exercise and I personally think something quite handy to have when slicing data.

As always if you have any questions or comments please leave them in the area below.

10 thoughts on “Creating a Slicer that also contains a DAX Measure”

        1. Hi Gilbert, Maxim , isn’t SUMMARIZECOLUMNS a bit “heavy” function to use inside ADDCOLUMNS? A simple ALL() would do the same:

          Slicer Table =
          ADDCOLUMNS ( ALL ( ‘All Web Site Data'[Country] ), “Sessionszz”, [Sessions] )

          or for that matter:

          Slicer Table =
          SUMMARIZECOLUMNS ( ‘All Web Site Data'[Country], “Sessionszz”, [Sessions] )

          I doubt it’ll matter in your case but still.

          That aside I think it’s marvelous idea to publish these DAX tricks for everyone to enjoy and learn from.

          1. Hi there,

            Will from the Microsoft Power BI Team suggested just adding in a calculated column on the table, which would then have the same outcome. I plan on updating this blog post to reflect this.

            Thanks for reading my blog it is appreciated.

  1. Gilbert, this is one of the most useful tips I’ve seen, and I have been using these tools a long time. Nice solution when screen real estate is limited.

    1. Hi Ed,

      Thanks for the kind words they are appreciated and glad to see that it has assisted you.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.