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

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
Medo1110
Regular Visitor

Filter Table in special way based on column from another table

Hi!

Im struggling to create a table which is filtered by value selected from another table:

I have 2 Tables: Customers and Coffeeshops. 

CustomerCoffeeshop IDReciept
A1 $                    20,00
A2 $                    30,00
A3 $                    10,00
B1 $                    15,00
B4 $                    30,00
C1 $                    10,00
D9 $                    20,00
D10 $                    20,00
E1 $                    30,00
E4 $                    50,00
E5 $                    20,00
F1 $                    10,00
H2 $                      4,00
H3 $                    25,00
H4 $                    12,00

Customer Table gives you an overview which customer ordered in which coffeeshop 

CofeeshopIDCoffeeshop Name
1Atlanta CS
2Los Angeles CS
3Seattle CS
4Barcelona CS
5Madrid CS
6London CS
7Berlin CS
8Amsterdam CS
9Paris CS
10Vienna CS

Coffeeshop Table is just an overview of all Coffeeshops.

What i want to achieve is: I want to select a coffeeshop: For example "Atlanta CS" . And my table should check if customer ordered in this coffeeshop AND in some another, and then give all customers IDs and Cofeeshop names where it was ordered except the one coffeeshop selected. Meaning i want to achieve something like this: 

Medo1110_0-1699013237581.png

Because Customer A ordered aswell as in Atlanta and in other 2, customer B in Atlanta and Barcelora etc. 

I thought i could achieve this by editing interactions: 

Screenshot 2023-11-03 131553.jpg

 Meaning I would filter customers that were  in Atlanta Coffeeshop and then i would pass only those customers to my Table. But it seems this doesnt work because if you select all customers, Table will also have Customer H for example. Plus im not sure how to exclude selected Coffeeshop after.

If you have any ideas i would appreciate any help! 
Thanks a lot in advance!



1 ACCEPTED SOLUTION
speedramps
Super User
Super User

Click here to download solution

Download PBIX 

 

speedramps_0-1699021987852.png

 

How it works ...

 

Create a related and unrelated copy of shop table.
Use one to pick (slice) and the other to display.
Otherwise it is complicated to slice a shop and display the other shops ! 

speedramps_1-1699022085668.png

 

Create a measure

 

Is Valid Customer = 
VAR pickshop = SELECTEDVALUE('Shop picklist'[CofeeshopID])    -- getspicked shop id
VAR customers =                                               -- get list of customers who visted picked shop    
CALCULATETABLE(
    VALUES(Sales[Customer]),
    ALL(Sales),
    Sales[Coffeeshop ID] = pickshop)
VAR thisshop = SELECTEDVALUE(Sales[Coffeeshop ID])             -- get shop being output
VAR thiscustomer= SELECTEDVALUE(Sales[Customer])               -- get customer being output
RETURN
SWITCH(TRUE(),                                                 
thisshop = pickshop, "N",                                       -- ignore picked shop
thiscustomer in customers, "Y"                                  -- flag customers who are in the list
)

 

 

Fiter you reports using the measure

 

Pay great care to use the correct table and field names.
iI you use the shop or the customer from the wrong table then the solutoon wont work.
Download my example and copy the code.

 

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers and you are receiving expert free help.
This solution works and does exactly what you asked.
So please be polite and quickly click the [accept as solution] and thumbs up and the button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

 

View solution in original post

2 REPLIES 2
Medo1110
Regular Visitor

Thanks a lot! This solution works exactly as needed

 

speedramps
Super User
Super User

Click here to download solution

Download PBIX 

 

speedramps_0-1699021987852.png

 

How it works ...

 

Create a related and unrelated copy of shop table.
Use one to pick (slice) and the other to display.
Otherwise it is complicated to slice a shop and display the other shops ! 

speedramps_1-1699022085668.png

 

Create a measure

 

Is Valid Customer = 
VAR pickshop = SELECTEDVALUE('Shop picklist'[CofeeshopID])    -- getspicked shop id
VAR customers =                                               -- get list of customers who visted picked shop    
CALCULATETABLE(
    VALUES(Sales[Customer]),
    ALL(Sales),
    Sales[Coffeeshop ID] = pickshop)
VAR thisshop = SELECTEDVALUE(Sales[Coffeeshop ID])             -- get shop being output
VAR thiscustomer= SELECTEDVALUE(Sales[Customer])               -- get customer being output
RETURN
SWITCH(TRUE(),                                                 
thisshop = pickshop, "N",                                       -- ignore picked shop
thiscustomer in customers, "Y"                                  -- flag customers who are in the list
)

 

 

Fiter you reports using the measure

 

Pay great care to use the correct table and field names.
iI you use the shop or the customer from the wrong table then the solutoon wont work.
Download my example and copy the code.

 

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers and you are receiving expert free help.
This solution works and does exactly what you asked.
So please be polite and quickly click the [accept as solution] and thumbs up and the button to leave kudos. 

One question per ticket please. If you need to change or extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramps in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

 

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

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

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.