Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet 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
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?
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
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? 🙂
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
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?
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
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?
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
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?
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
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
86 | |
82 | |
70 | |
49 |
User | Count |
---|---|
143 | |
122 | |
109 | |
60 | |
55 |