Filtering / Visual Level Fitlers for Slicers

[Anonymous]

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]

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]

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]

Hi @Anonymous,

What is the formula you are using for achieving blank?

Regards,

MFelix

[Anonymous]

@MFelix,

Below is everything listed out

Count of Market = Me directly selecting the "Country" columns and selecting "Distinct Count" Selected ... This would = the first half of your formula you suggested. (Distinctcount = Geography[Market]) = 10

test = CALCULATE(DISTINCTCOUNT(Geography[Market]),ALL(Geography[Market])) ---- (2nd Half of Formula you suggested) = 13

Blank Slicer = IF(DISTINCTCOUNT(Geography[Market])=CALCULATE(DISTINCTCOUNT(Geography[Market]),ALL(Geography[Market])),"",SUM('Retailer POS'[POS USD TY])) --- Full Formula you suggested is showing the totals for everything

This is saying that the first half of the formula is not equal to the second half 10 not equal to 13... Therefore sum the POS data...

Please Advise!

[MFelix]

Hi @Anonymous,

Do you want to let the user select more than one country on your slicer or only one?

If it's only one you should replace the second part by 1 and drop the formula.

Regards,

MFelix

[Anonymous]

@MFelix

If possible I would like to select more than one option on the slicer ... Thank you for all your help!

[MFelix · Accepted solution]

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