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 have a list of events. Each event is related to a product. I want to product reports on :
1. All sales
2. All sales of the highlighted product of an event.
3. All sales of users who attended an event.
I have a slicer based off the Events table.
I can get all sales by disconnecting the visual from the slicer.
I can get all sales of the attended users through my relationships.
But I can not get all sales of the hightlighted product.
I have added a custom column in the sales table to bring in the event ID if the product sold relates to an event.
See below. "Targeted Product column" is a 1, 2 or nothing. A 1 means it relates to the April event, a 2 means the May Event and nothing means it does not relate to an event. The product ID related to the April event is 10.
So in "Hightlighted Product Sales" I want to show all product 10s (or anything with a 1 in the Targeted Product Column).
If I could filter on the dynamic slicer it could work.
Any ideas? I appreciate the help.
I think I am getting closer.
I have a column on the sales table which displays the "Event Key" the sale is related to (if any).
Then I have a measure in the sales table to display the "Event Key" of the selected event in the slicer.
I am trying to write a anther measure : if (column=measure, 1, 0). Then I can filter out the 1/0. However this does not work because a measure acts on the whole table.
Any idea how I can test for the 2 fields Column: "Event Key" and Measure: "Filter" to be equal? I want to filter on the matches. Is this even possible?
Thank you!
@jlostrom , You can filter selected product from the event table for sales like
calculate(Sum(Sales[Total Sales]) , filter(Sales, Sales[Product Code] in values(events[Product Code]) ))
You can also consider treatas
https://learn.microsoft.com/en-us/dax/treatas-function
Thank you for the suggestions.
The calculate/sum/filter does not filter on the event selected in the slicer. It sums any product linked to any event, not the single event selected in the slicer.
I also tried the TREATAS but it returns nothing.
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 |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |