五月 23, 2019

How to set default value for slicers in POWER BI using DAX

作者 ladydumbo

As my first blog, I want to talk about how to set default value for slicers in Power BI using DAX functions.

Recently, I was making a power bi report that contains the sales figure from 2010 to 2019 for a customer. I was asked to make the report show the figures of the current year by defalult instead of all the passed years, which means that I need to set default values for the date slicer selections.

A default value = the value when there is no selection in the slicer. Once there has one selection in the slicer, the default value should be killed. If there is no selection in the slicer, there is no direct filter on the column behind the slicer, which makes me think of ISFILTERED function immediately.

ISFILTERED Returns TRUE when columnName is being filtered directly. If there is no filter on the column or if the filtering happens because a different column in the same table or in a related table is being filtered then the function returns FALSE.

So here we go, let’s use ISFILTERED function to complete the mission.

Here is my data sample: I have a sale table and a calender table.

Now comes the stars, a year slicer and a histogram show the total sale by salesperson ID.

A year slicer using ‘Calender'[Year],
and a histogram show the [total sale] by ‘sales'[salesperson ID].

I need to make sure when there is no selection in the year slicer, the histogram will show the total sale of 2014, which comes naturally:

Total sales with Default = 
IF (
    ISFILTERED(Calender[Year]);
   //whether the year column has been filtered directly
    
    SUM ('Sales SalesOrderHeader'[SubTotal]);
    CALCULATE (
        SUM ( 'Sales SalesOrderHeader'[SubTotal] );
        FILTER('Calender';Calender[Year]=2014)
    )  
)

So simple, right ? Maybe that’s the reason why Power BI doesn’t provide us with a default value for a slicer : too simple to realise.

What if … I have more than one slicers need to be set a defaults value? Like the situation below:

Three slicers: ‘Calender'[Year] / ‘Calender'[Month] / ‘Calender'[Date]

Always ISFILTERED ( ) always simple, just need a help from OR ( ).by creating a measure [IsAnySlicerFiltered?], who will return False when there is no selection in any of these three slicers, and will return True when there has one.

IsAnySlicerSelected? = 
 OR(
    OR(
         ISFILTERED(Calender[Date]);ISFILTERED(Calender[Month]));
    ISFILTERED(Calender[Year])
 )

Now you replace [IsAnySlicerFiltered?] with ISFILTERED(Calender[Year]); in the earlier measure [Total sales with Default].

Total sales with Default (miltiple slicers) = 
IF (
    [IsAnySlicerSelected?];
    SUM ('Sales SalesOrderHeader'[SubTotal]);
    CALCULATE (
        SUM ( 'Sales SalesOrderHeader'[SubTotal] );
        FILTER('Calender';Calender[Year]=2014)
    ) 
)

Everything is done !

Absolutly this is not the only way to set the defalts value for a slicer, we can think another way, like:

There is no selection in the slicer => The number of rows of the column behind it should be 0 => Ok, it seems COUNTROW( ) could do this for us.

You need to create a measure to note your current selection in the slicer, we call it [CurrentSelection?]

CurrenctSelection? = COUNTROWS(DISTINCT(ALLSELECTED(Calender[Year])))

And another one named [TotalSelection?]

TotalSelection? = COUNTROWS(DISTINCT(ALL(Calender[Year])))

Et voilà, if [CurrentSelection?] = [TotalSelection?] situation happends when there is no selection or you select “select all” option in the slicer. By the way, neither of these two ways of setting the default value doesn’t work when you selected the “select all” option in the slicer !

Total sales with Default = 
IF (
[CurrenctSelection?] = [TotalSelection?];
    CALCULATE (
        SUM ( 'Sales SalesOrderHeader'[SubTotal] );
        FILTER('Calender';Calender[Year]=2014)
    );
    SUM ('Sales SalesOrderHeader'[SubTotal])
)

Thanks for watching !