March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
Hello,
I Have a Table that is similar to the one below:
Product ID | Product Type | Product Location | Product Color |
A | WIDGET | NY | BLUE |
B | WIDGET | CA | RED |
C | WIDGET | NYC | BLUE |
D | SERVER | CA | GREEN |
E | SERVER | CA | GREEN |
F | PC | FL | RED |
What I'm trying to do is set the filter on a dashboard by Product ID, where it displays all Products that share the same Product Type, Product Location and Product Color.
For Example:
If I Select Project A, it will display:
Product ID | Product Type | Product Location | Product Color |
A | Widget | NY | Blue |
C | Widget | NY | Blue |
Any help will be greatly appreciated.
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
I have created a sample for your reference. Please check the following steps as below.
1. Create a calculated table and make it as slicer.
Slicer = DISTINCT(Query1[Product ID])
2. New a measure and make the table viusal filtered by it.
Measure =
VAR pro =
SELECTEDVALUE ( Slicer[Product ID] )
VAR color =
CALCULATE (
MAX ( Query1[Product Color] ),
FILTER ( ALL ( Query1 ), Query1[Product ID] = pro )
)
VAR loc =
CALCULATE (
MAX ( Query1[Product Location] ),
FILTER ( ALL ( Query1 ), Query1[Product ID] = pro )
)
VAR ty =
CALCULATE (
MAX ( Query1[Product Type] ),
FILTER ( ALL ( Query1 ), Query1[Product ID] = pro )
)
RETURN
IF (
MAX ( Query1[Product Color] ) = color
&& MAX ( Query1[Product Location] ) = loc
&& MAX ( Query1[Product Type] ) = ty,
1,
0
)
Pbix as attached.
Hi @Anonymous ,
I have created a sample for your reference. Please check the following steps as below.
1. Create a calculated table and make it as slicer.
Slicer = DISTINCT(Query1[Product ID])
2. New a measure and make the table viusal filtered by it.
Measure =
VAR pro =
SELECTEDVALUE ( Slicer[Product ID] )
VAR color =
CALCULATE (
MAX ( Query1[Product Color] ),
FILTER ( ALL ( Query1 ), Query1[Product ID] = pro )
)
VAR loc =
CALCULATE (
MAX ( Query1[Product Location] ),
FILTER ( ALL ( Query1 ), Query1[Product ID] = pro )
)
VAR ty =
CALCULATE (
MAX ( Query1[Product Type] ),
FILTER ( ALL ( Query1 ), Query1[Product ID] = pro )
)
RETURN
IF (
MAX ( Query1[Product Color] ) = color
&& MAX ( Query1[Product Location] ) = loc
&& MAX ( Query1[Product Type] ) = ty,
1,
0
)
Pbix as attached.
Thank You Very Much it Worked.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Your insights matter. That’s why we created a quick survey to learn about your experience finding answers to technical questions.
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
124 | |
89 | |
84 | |
70 | |
51 |
User | Count |
---|---|
206 | |
143 | |
97 | |
79 | |
68 |