Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
LadyLizzy
Frequent Visitor

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

Hi, I'm stuck!

I have these relationships:

TicketTypes_relationships.PNG

My initial UI:

TicketTypes_UI.PNG

 

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:

TicketTypes_FilterMovies.PNG

 

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

TicketTypes_TwoChosen.PNG

 

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?

9 REPLIES 9
v-lili6-msft
Community Support
Community Support

hi @LadyLizzy 

Please adjust the report as below:

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

1.JPG

 

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

 

Regards,

Lin

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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:

still wrong.PNG

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? 🙂

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

Community Support Team _ Lin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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
Community Champion
Community Champion

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. 

image.png

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


Connect on LinkedIn

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?

TicketTypes_relationships.PNG

 

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":

TicketTypes_TwoChosen.PNG

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

tex628
Community Champion
Community Champion

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. 

image.png

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 


Connect on LinkedIn

Yes, but then the TicketTypes dublicate instead:

TicketTypes_doubles.PNG

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
Community Champion
Community Champion

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!

image.png

Br,
Johannes


Connect on LinkedIn

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.