Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hi everyone,
I’m working with a dataset where I need to apply multiple slicers using AND logic to filter rows in a matrix visual.
Here’s the setup:
I need a solution where both slicers work together to filter the matrix based on this AND logic, while still showing data from other categories.
Issue: Using the slicers does not filter the dataset just by the rows where Category = 'Apartment - GSF' and Category = 'Original Pricing Date', rather it filters by all values of the matrix. It works when creating two matrices, one where Category is Apartment - GSF and one where Category is Original Pricing Date; however, this is not the desired output because I need to the data from the other categories too and all in one matrix. I’ve tried creating measures to apply this logic, but Power BI requires aggregation functions (like MAX or MIN) in measures, which breaks the row-level filtering I need. I need to check if the values for each project match the slicer ranges without using aggregation functions, but I haven’t been able to get this working.
Any guidance on how to achieve this would be much appreciated!
Solved! Go to Solution.
Hi @cashcrane
Based on your description, you can refer to the following solution.
Sample data.
1.Create two new table.
Parameter = GENERATESERIES(0, 10, 0.1)
Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))
There is no relationship among the tables.
2.Create the following measure.
Measure2 =
VAR _filter =
FILTER (
ALLSELECTED ( 'Table' ),
[Group1]
IN VALUES ( 'Table'[Group1] )
&& [Group2]
IN VALUES ( 'Table'[Group2] )
&& [Project] IN VALUES ( 'Table'[Project] )
)
VAR _count1 =
COUNTROWS (
FILTER (
_filter,
'Table'[Category] = "Apartment - GSF"
&& 'Table'[Value-GSF] >= MIN ( Parameter[Value] )
&& 'Table'[Value-GSF] <= MAX ( Parameter[Value] )
)
)
VAR _count2 =
COUNTROWS (
FILTER (
_filter,
'Table'[Category] = "Original Pricing Date"
&& 'Table'[Value-OPD] >= MIN ( 'Calendar'[Date] )
&& 'Table'[Value-OPD] <= MAX ( 'Calendar'[Date] )
)
)
RETURN
IF (
SELECTEDVALUE ( 'Table'[Category] ) = "Apartment - GSF"
|| SELECTEDVALUE ( 'Table'[Category] ) = "Original Pricing Date",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( 'Table', _count1 > 0 && _count2 > 0 )
),
CALCULATE ( SUM ( 'Table'[Value] ) )
)
3.Create two slicers, put the paramater of the paramater table to one slicer, and put the date field of the calendar table to the other. then create the matrix, and put the following field to the matrix.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @cashcrane
Based on your description, you can refer to the following solution.
Sample data.
1.Create two new table.
Parameter = GENERATESERIES(0, 10, 0.1)
Calendar = CALENDAR(DATE(2024,1,1),DATE(2024,12,31))
There is no relationship among the tables.
2.Create the following measure.
Measure2 =
VAR _filter =
FILTER (
ALLSELECTED ( 'Table' ),
[Group1]
IN VALUES ( 'Table'[Group1] )
&& [Group2]
IN VALUES ( 'Table'[Group2] )
&& [Project] IN VALUES ( 'Table'[Project] )
)
VAR _count1 =
COUNTROWS (
FILTER (
_filter,
'Table'[Category] = "Apartment - GSF"
&& 'Table'[Value-GSF] >= MIN ( Parameter[Value] )
&& 'Table'[Value-GSF] <= MAX ( Parameter[Value] )
)
)
VAR _count2 =
COUNTROWS (
FILTER (
_filter,
'Table'[Category] = "Original Pricing Date"
&& 'Table'[Value-OPD] >= MIN ( 'Calendar'[Date] )
&& 'Table'[Value-OPD] <= MAX ( 'Calendar'[Date] )
)
)
RETURN
IF (
SELECTEDVALUE ( 'Table'[Category] ) = "Apartment - GSF"
|| SELECTEDVALUE ( 'Table'[Category] ) = "Original Pricing Date",
CALCULATE (
SUM ( 'Table'[Value] ),
FILTER ( 'Table', _count1 > 0 && _count2 > 0 )
),
CALCULATE ( SUM ( 'Table'[Value] ) )
)
3.Create two slicers, put the paramater of the paramater table to one slicer, and put the date field of the calendar table to the other. then create the matrix, and put the following field to the matrix.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hey Yolo Zhu, thank you so much for your solution!
I only had to add one line in order to be able to get the project to drop off the matrix as well (I also changed the calendar parameter to be another decimal value parameter because I was having some issues with the varying date types in the same column, but this is close enough).
Here's the final code:
MeasureNEW =
VAR _filter =
FILTER (
ALLSELECTED ( 'NumericData' ),
[Group1] IN VALUES ( 'NumericData'[Group1] )
&& [Group2] IN VALUES ( 'NumericData'[Group2] )
&& [Attribute] IN VALUES ( 'NumericData'[Attribute] )
)
VAR _count1 =
COUNTROWS (
FILTER (
_filter,
'NumericData'[Category] = "Apartment - GSF"
&& 'NumericData'[Value - GSF] >= MIN ( Parameter[Value] )
&& 'NumericData'[Value - GSF] <= MAX ( Parameter[Value] )
)
)
VAR _count2 =
COUNTROWS (
FILTER (
_filter,
'NumericData'[Category] = "Apartment - RSF"
&& 'NumericData'[Value - RSF] >= MIN ( 'RSF'[Value] )
&& 'NumericData'[Value - RSF] <= MAX ( 'RSF'[Value] )
)
)
RETURN
IF (
_count1 > 0 && _count2 > 0,
CALCULATE (
SUM ( 'NumericData'[Value] )
),
BLANK() -- Hides the project column when the conditions are not met
)
User | Count |
---|---|
141 | |
70 | |
69 | |
53 | |
52 |
User | Count |
---|---|
209 | |
92 | |
64 | |
60 | |
57 |