Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Filtering / Visual Level Fitlers for Slicers

Multi Layered Question!

 

New to Power Bi but I want to create a report that has multiple options for filtering and to change the sales figure based off of each filter selection. These filters are not created off of measures but columns within my table. (Ex. Country, Department, Time Frame)

 

Question 1: How can I make it where my sales figures show as "Blank" until something within my slicer is selected ? Currently shows the total figure which won't make sense to the report viewers without a filter selected.

 

Question 2: How can I remove filter options without effecting the sales totals. (Ex. I would like to remove some low producing departments to lower the slicer options but still include them in the overall Sales figure totals for totals to match.

 

If anyone needs more detail I can provide it.

  • MFelix's avatar
    MFelix
    8 years ago

    HI Anonymous,

     

    Don't know if you want to use all the select values on the slicer but try something like this:

     

    Sales WITH FILTER = 
IF (
    DISTINCTCOUNT ( Sales[Country] )
        > 10 ;
    BLANK ();
    SUM ( Sales[Sales] )
)

    Replace 10 by the maximum number of selections on the slicer you want to have before blank.

     

    Regards,

    Mfelix

7 Replies

  • MFelix's avatar
    MFelix
    Super User
    8 years ago

    Hi Anonymous,

     

    You need to understand that the measures in DAX are based on context so with the filters / slicers and the parameter within formulas you can change the context of your data.

     

    I made a mockup of a table with Sales with your 3  columns: Country, Department and Value although I don't have the full extend o your model you just need to adjust the measures below to accomodate your filters.

     

    Question 1:

    You need to create a measure like the one below I only doing this for a single filter / slicer but if you need more than one filter to interact you can adjust it

     

    Sales WITH FILTER =
IF (
    DISTINCTCOUNT ( Sales[Country] )
        = CALCULATE ( DISTINCTCOUNT ( Sales[Country] ); ALL ( Sales[Country] ) );
    BLANK ();
    SUM ( Sales[Sales] )
)

     

    • DistinctCount - Returns number of countrys selected on slicer
    • Calculate Distinct Count - Returns the total number of countries in the table
    • If both of the previous values are equal it will return blank

    If you want to have a single selection formula should be adapted to:

    Sales WITH FILTER = 
IF (
    DISTINCTCOUNT ( Sales[Country] )
        > 1 ;
    BLANK ();
    SUM ( Sales[Sales] )
)

    Question 2:

    You need to create a measure based on the slicer you want to reduce and consider all the information

     

    Sales total = CALCULATE([Sales WITH FILTER]; ALL(Sales[Department]))

    On my example I'm reducing the sales department slicer so the result changes. I'm using the previous measure to make this new one.

     

    See below the result:

     

     

    This are some example measures they need to be adjusted to your data model and accordingly to your setup.

     

    Regards,

    MFelix

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Thank you MFelix... I think we are very close on Question 1... It will show blank when nothing is selected but when something is selected it will still show blank...I don't know if this will change your approach or not but a couple things to mention: There is a report level filter in place taking out two countries from the slicer, and the country data and the sales data are in two different tables but are related within the report. Please advise!

      • MFelix's avatar
        MFelix
        Super User
        8 years ago

        Hi Anonymous,

         

        What is the formula you are using for achieving blank?

         

        Regards,

        MFelix