Filter table (hierarchy slicer AND logic) and filter related table accordingly

[LadyLizzy]

Hi, I'm stuck!

I have these relationships:

My initial UI:

To filter my movies table with AND logic instead of OR I did my best to follow this blog post: https://medium.com/seismic-data-science/changing-or-to-and-logic-for-power-bi-slicers-1a6b20aee5f5

My measures:

HierarchySlicer CategoryCheck =
IF([SelectedCategoriesCount] = 0; 1;
IF(DISTINCTCOUNT(Categories[SelectedValue]) = [SelectedCategoriesCount]; 1; 0))

HierarchySlicer FilterTicketTypes =
CALCULATE(SUM(TicketTypes[MovieId]);
FILTER(TicketTypes; [HierarchySlicer CategoryCheck] = 1))

HierarchySlicer TicketTypeCount =
CALCULATE(COUNTROWS(TicketTypes);
FILTER(TicketTypes; [HierarchySlicer CategoryCheck] = 1))

I apply the HierarchySlicer CategoryCheck on the Movies table:

It filters the movies table correctly but the related TicketTypes tables is not filtered accordingly

I thought that the relationship would be enough to filter the TicketTypes according to the filtered Movies table but everything I have tried to manipulate this behaviour hasn't worked. Are there any experts out there that can see what's wrong or missing?

[v-lili6-msft]

hi @LadyLizzy 

Please adjust the report as below:

1. change the cross filter direction from "Single" to "Both" Between "Movies"table and "TicketTypes" table

2.apply the [HierarchySlicer CategoryCheck] on the "TicketTypes" tables visual too.

Regards,

Lin

[LadyLizzy]

Hi Lin, thank you so much for answering 🙂

Unfortunately that didn't do the trick, it still (incorrectly) sums up both of the categories in the TicketType list, even though the MovieName list is empty:

It's weird because I don't understand how it gets the 119878 sum, if I only have the first one ticked it has 12529 and the second one has 110536, those summed are 123065, so 3187 too little. Do you have any more ideas? 🙂

[v-lili6-msft]

hi @LadyLizzy 

Could you please share a simple sample pbix file for us have a test, it will be a great help for us to solve the case.

Regards,

Lin

[LadyLizzy]

Thank you, I have made a simplyfied version here: TicketTypesReport.pbix 

Another problem that comes to light is that the ISFILTERED doesn't work when all subcategories are chosen. I searched the forum but couldn't find a solution for that. It may be part of the problem?

[tex628]

Hello there, 

I actually think your example is working as intended. The logic that you have implemented is not supposed to filter ticket type by movie but filter ticket type by category. 

Essentially the reason you get 0 movies and alot of tickets in your picture is that there are no movies that are both Child and Adult while every ticket appears to be available for both Child and adult. 



Taking a look at this picture, you can see that there are no movies that are both "Child" and "Med tillägg". 
But looking at the tickets Barnbiljett NY is sold for both Smallfoot & Fantastiska Skabninger which means that both the categories exists for that specific ticket. 

Hope this helps! 

/ J

[LadyLizzy]

Hello, thank you for contributing 🙂

The inteded functionality is to filter ticket types according to the filtered movies. The categories shall filter the movies and thereby the ticket types. The ticket types have no direct relation to the categories, they only have to the movies and it's not possible to sell tickets without any movies.

There might be something wrong or missing in the report/data structure?

Another problem is the category to movie filtering:

When all subcategories in a category is selected it is perceived to be the same as none are selected. The issue is also described in this post: https://community.powerbi.com/t5/Desktop/ISFILTERED-function-not-working-when-select-all-is-selected...

The SelectedCategoriesCount is 0, but should be 2, since two items are ticked and the movies list should be empty because no movies have both "Barn" and "Voksen":

My question here is if there is another way to do this or any workarounds? 

[tex628]

If you add the movie name to the ticket type table the filter on ticket type will be evaluate ticket type together with movie name which should give you the correct result. 



The second issue is much more complicated. The reason this happens is that when all categories in a slicer is selected the table that's being sliced is not actually being filtered. This means that the ISFILTERED statement in your measure is returning FALSE and the measure returns 0. 

I think the first step should be to check if there are movies that contain all the options in a group. Generally speaking I guess it shouldn't be possible for a signle movie to be classified as both an adult and a child movie. If that functionality is really needed I believe that you most likely need to make some changes to your Categories table to make it work. 

/ Johannes 

[LadyLizzy]

Yes, but then the TicketTypes dublicate instead:

Which is worse because it's the TicketTypes that is the whole point of the report. 

Any more ideas? Is there perhaps something wrong with the tables?

[tex628]

Alright,  

Remove the name column and the filter from the ticket types table. 

Replace the sum of count with this measure:

Count of ticket = 
CALCULATE(
    SUM(TicketTypes[Count]);
    FILTER(
        Movies;
        [HierarchySlicer CategoryCheck] = 1
    )
)

This should return the correct number of tickets related to the movies currently being filtered!



Br,
Johannes