Slicer affected by other slicer

[AliPoTD]

Hello all, 

Can anyone please help with a query I am trying to resolve?

I have a table visual which is controlled by a slicer.

The table visual shows the sums of various order quantities by customer, obtained from an Order data table.

The table visual is controlled by a "Year" slicer, which is taken from the "Date" field of the orders from the Order data table.

I have been asked to adapt this with a further slicer to only show values in the Table visual for customers who have orders in all of the selected Years (from the Year slicer).

How can I achieve this? Can a slicer be affected by another slicer?

Many thanks.

[Bibiano_Geraldo · Accepted solution]

Hi @AliPoTD ,

Create this measure and drag and drop on visual level filters, and filter when is 1:

Valid Customers = 
VAR teste = 
    CALCULATE(
        DISTINCTCOUNT('Calendar'[Year]),
        ALLSELECTED('Calendar'[Date].[Year])
    )

VAR teste2 = 
    CALCULATE(
        DISTINCTCOUNT('Calendar'[Year]),
        ALLEXCEPT(Orders,'Calendar'[Year])
    )

VAR result = teste = teste2


RETURN
IF(
    result,
    1,
    0
)

Let me know if its all ok.

[AliPoTD]

Hi @Bibiano_Geraldo ,

Please could I ask for your further asistance regarding the above? 

I was able to get your above logic working but I now need to adapt it further and it's falling over. I don't understand why, would you mind sharing your expert knowledge please?

I have a test file at Test Data2.pbix with the scenario I need.

I'd be grateful if you could share the logic behind the code if you wouldn't mind - I'm fairly new to this and want to understand what's happening so I can learn for the future but I am at a bit of a loss over this!

Many thanks for any assistance you can share to get this working.

[Bibiano_Geraldo · Accepted solution]

Hi @AliPoTD ,
I tried to add comments to explain the purpose of measure:

Valid Customer2s = 
-- Step 1: Calculate the total number of distinct years selected in the slicer (from Calendar[CalendarYear])
VAR totalSelectedYears = 
    CALCULATE(
        DISTINCTCOUNT('Calendar'[CalendarYear]),  -- Count distinct years from the 'Calendar' table
        ALLSELECTED('Calendar'[CalendarYear])     -- Take into account the years selected in the slicer (ignoring other filters)
    )

-- Step 2: Calculate the total number of distinct years for the selected farm
VAR totalYearsForSelectedFarm = 
    CALCULATE(
        DISTINCTCOUNT('Calendar'[CalendarYear]),  -- Count distinct years from the 'Calendar' table
        FILTER(                                  -- Apply additional filtering based on the selected farm
            ALLSELECTED('Litres'),                -- Consider the 'Litres' table filtered by the slicer
            Litres[Farm Number] = SELECTEDVALUE(Litres[Farm Number])  -- Filter only the rows for the selected farm
        )
    )

-- Step 3: Compare the total number of years selected in the slicer with the total years for the selected farm
VAR result = totalSelectedYears = totalYearsForSelectedFarm

-- Step 4: Return 1 if the total years for the farm match the selected years, otherwise return 0
RETURN
IF(
    result,       -- Check if the condition in 'result' is TRUE (both totals match)
    1,            -- If TRUE, return 1
    0             -- If FALSE, return 0
)

Finaly result:

[AliPoTD]

Amazing, thank you so much. This makes sense and I've been able to replicate it in my report.

Thanks so much for your quick response too, very much appreciated 🙂

[Bibiano_Geraldo]

Happy it works and i hope your understood the logic behind this measure.

[Bibiano_Geraldo]

Hi @AliPoTD ,

Assume that the Orders table contains a Customer field and a Date field, create a new measure to count the years each customer has orders:

CustomerYearsCount = 
CALCULATE(
    DISTINCTCOUNT('Orders'[Year]), 
    ALLEXCEPT('Orders', 'Orders'[Customer])
)

Now, create another measure that counts the total number of years selected in the slicer.

SelectedYearsCount = 
DISTINCTCOUNT('Orders'[Year])

Now, create a measure that checks if the customer’s count of distinct years is equal to the total number of selected years.

CustomerHasOrdersInAllSelectedYears = 
IF(
    [CustomerYearsCount] = [SelectedYearsCount], 
    1, 
    0
)

Add the CustomerHasOrdersInAllSelectedYears measure to the table visual’s filters pane and set the filter condition to show only values where the measure equals 1. This ensures that only customers who have orders in all the selected years will be displayed in the table.

[AliPoTD]

Many thanks for your quick response.

However, for the CustomerYearsCount measure, i am getting the same value for all Customers, a total of 6 due to their being 6 years of data. It isn't showing it per Customer, but instead for the entire data table? 

[Bibiano_Geraldo]

Hi @AliPoTD ,

Try to replace the measure with this one:

CustomerYearsCount = 
CALCULATE(
    DISTINCTCOUNT('Orders'[Year]), 
    FILTER(
        ALL('Orders'[Year]), 
        'Orders'[Customer] = MAX('Orders'[Customer])
    )
)

let me know if it works, if no, please consider to share no sensitive data and desired output

[AliPoTD]

Thank you Bibiano_Geraldo. What's the best way to send you the .pbix dummy file?

[Bibiano_Geraldo]

Hi @AliPoTD ,

You can share the link of onedrive or other cloud, here in comments.

[AliPoTD]

Hi @Bibiano_Geraldo , were you able to access the example PBI file from my one drive?

Thanks.

[Bibiano_Geraldo · Accepted solution]

Hi @AliPoTD ,

Create this measure and drag and drop on visual level filters, and filter when is 1:

Valid Customers = 
VAR teste = 
    CALCULATE(
        DISTINCTCOUNT('Calendar'[Year]),
        ALLSELECTED('Calendar'[Date].[Year])
    )

VAR teste2 = 
    CALCULATE(
        DISTINCTCOUNT('Calendar'[Year]),
        ALLEXCEPT(Orders,'Calendar'[Year])
    )

VAR result = teste = teste2


RETURN
IF(
    result,
    1,
    0
)

Let me know if its all ok.

[AliPoTD]

Thank you so much for your help, this has worked, very much appreciated!

Rather than having a second slicer which, I understand, won't work with Measures, I applied this to the visual filters of the table which has worked. I have therefore created 2 tables; one with the filter to show Like for Like data and one without to show all data, and have used bookmarks for the user to select which type of table they can view.

Perfect, thanks so much for your help with this 🙂

[Bibiano_Geraldo]

Happy this worked @AliPoTD, I'm here to help...

[Bibiano_Geraldo]

Hi @AliPoTD , i've lost the post, now working on your file, i'll reply you soon

Thank you

[AliPoTD]

Please see the dummy file:

Test Data.pbix