Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I'm stuck on what I believe is to be a relatively easy subject but for some reason I can't seem to find the right way to get to my desired result:
I've got a fact table of ticket sales and a dimension table of productions connected by a productionID.
What I'm looking to do is to set a filter on a specific production and get a list of the top x productions that have been booked by the individuals that have booked a ticket for the filtered production.
What this means is that I am filtering a table of BookerIDs by production and then subsequently need to get a list of productions that are related to those filtered BookerIDs and get a count of BookerIDs per production that is filtered by the filtered table of BookerIDs.
I also realize that it might be neccesary to use a bidirectional filter in order to filter back from the bookerIDs to the productions.
I'm not sure if I would have to use the booker dimension table seeing as though I do a count of the bookerIDs (from the fact.transaction table).
This would answer the business case of: What are the top 10 productions that were visited by the people that bought a ticket for production Y.
TABLES:
Dim.production:
productionID | Production |
1 | Titanic |
2 | Jungle Book |
3 | The Godfather |
4 | Batman |
5 | Superman |
6 | Spiderman |
7 | Aladin |
Dim.booker:
bookerID | BookerName |
1 | Jack |
2 | Stacy |
3 | Robert |
4 | Anne |
5 | Ivette |
6 | Peter |
7 | John |
8 | Dirk |
9 | Gloria |
10 | Helen |
Fact.ticketsales:
PersonID | ProductionID |
9 | 5 |
10 | 7 |
6 | 2 |
8 | 5 |
9 | 4 |
6 | 5 |
8 | 3 |
DESIRED RESULT:
Filter: Superman
Count of PersonID | Production |
1 | Batman |
1 | The Godfather |
1 | Jungle Book |
Solved! Go to Solution.
Hi,
I suggest having a disconnected production slicer table like the attached sample file.
Please check the below picture and the attached pbix file.
Count of person measure: =
VAR list_bookerIDs =
CALCULATETABLE (
VALUES ( Ticketsales[bookerID] ),
FILTER (
ALL ( Ticketsales ),
Ticketsales[productionID] = SELECTEDVALUE ( 'Production Slicer'[productionID] )
)
)
VAR list_lookerIDs_product =
FILTER (
Ticketsales,
Ticketsales[bookerID]
IN list_bookerIDs
&& Ticketsales[productionID] <> SELECTEDVALUE ( 'Production Slicer'[productionID] )
)
RETURN
COUNTROWS ( SUMMARIZE ( list_lookerIDs_product, Ticketsales[bookerID] ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I suggest having a disconnected production slicer table like the attached sample file.
Please check the below picture and the attached pbix file.
Count of person measure: =
VAR list_bookerIDs =
CALCULATETABLE (
VALUES ( Ticketsales[bookerID] ),
FILTER (
ALL ( Ticketsales ),
Ticketsales[productionID] = SELECTEDVALUE ( 'Production Slicer'[productionID] )
)
)
VAR list_lookerIDs_product =
FILTER (
Ticketsales,
Ticketsales[bookerID]
IN list_bookerIDs
&& Ticketsales[productionID] <> SELECTEDVALUE ( 'Production Slicer'[productionID] )
)
RETURN
COUNTROWS ( SUMMARIZE ( list_lookerIDs_product, Ticketsales[bookerID] ) )
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
That is a beautiful solution that I wouldn't ever have thought of.
I will apply it to my datamodel tomorrow and when I've got it working, I'll mark your answer as the solution.
Thank you so much!
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |