cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
jlostrom
Frequent Visitor

Filtering a table based off a slicer selection

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.

jlostrom_0-1669847526638.png

 

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.

 

jlostrom_3-1669848157636.png

Any ideas? I appreciate the help.

 

3 REPLIES 3
jlostrom
Frequent Visitor

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?

 

jlostrom_0-1669912900581.png

 

Thank you!

 

amitchandak
Super User
Super User

@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.

CALCULATE( SUM(sales[Total Sales]),  TREATAS(VALUES(Event[Product code]),sales[Targeted Product])

Helpful resources

Announcements
Carousel_PBI_Wave1

2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

Power BI Summit Carousel 2

Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

BizApps LATAM 2023

Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

Power Platform Bootcamp

Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.