Forum Discussion
Slicer Setting limit the matrix?
Hi JosephWu ,
I created some data:
If your slicer and matrix are related, then you select 2022.1.1 – 2022.2.1 in the slicer, only the date filtered by the slicer will appear between the matrices, if you want to display the date not selected in the slicer according to your own custom rules, you still need to have a date column that is not related to the data in the matrix.
After that, use the IF method to judge and put it in the filter to set.
1. Create measure.
Flag = var _selectyear=SELECTEDVALUE('Year_Slicer'[Year]) var _selectqu=SELECTEDVALUE('Qu_Slicer'[Quarter]) return IF( AND( YEAR(MAX('Table'[Date])) =_selectyear ,QUARTER(MAX('Table'[Date]))>=_selectqu-1&&QUARTER(MAX('Table'[Date]))<=_selectqu) || AND( YEAR(MAX('Table'[Date])) =_selectyear -1 ,QUARTER(MAX('Table'[Date]))=_selectqu),1,0)2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi JosephWu ,
I created some data:
If your slicer and matrix are related, then you select 2022.1.1 – 2022.2.1 in the slicer, only the date filtered by the slicer will appear between the matrices, if you want to display the date not selected in the slicer according to your own custom rules, you still need to have a date column that is not related to the data in the matrix.
After that, use the IF method to judge and put it in the filter to set.
1. Create measure.
Flag =
var _selectyear=SELECTEDVALUE('Year_Slicer'[Year])
var _selectqu=SELECTEDVALUE('Qu_Slicer'[Quarter])
return
IF(
AND(
YEAR(MAX('Table'[Date])) =_selectyear ,QUARTER(MAX('Table'[Date]))>=_selectqu-1&&QUARTER(MAX('Table'[Date]))<=_selectqu)
||
AND(
YEAR(MAX('Table'[Date])) =_selectyear -1 ,QUARTER(MAX('Table'[Date]))=_selectqu),1,0)2. Place [Flag]in Filters, set is=1, apply filter.
3. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi, Anonymous
Thank your reply, it's helpful!
In normal or simple solution,I think I have gotten the correct way to solve that.
But I try to combine that in my solution,still having some problem.
My matrix use the Fact_sale table(Sum order) relationship with Dim_calender(Date By Date)
and I use the Dim_calender (4) being the slicer which single table NO Relationship from any table.
Matrix filter with this measure
#timeTest =
var _selectyear=SELECTEDVALUE('DIM_Calendar (4)'[Year])
var _selectqu=SELECTEDVALUE('DIM_Calendar (4)'[QuarterNumber])
return
if(_selectqu -1 = 0,
if(
AND( YEAR(MAX('DIM_Calendar'[Date])) =_selectyear -1 ,QUARTER(MAX('DIM_Calendar'[Date]))>=4||QUARTER(MAX('DIM_Calendar'[Date]))<=_selectqu)
||
AND(YEAR(MAX('DIM_Calendar'[Date])) =_selectyear,QUARTER(MAX('DIM_Calendar'[Date]))=_selectqu)
,1,0
),if(AND( YEAR(MAX('DIM_Calendar'[Date])) =_selectyear ,QUARTER(MAX('DIM_Calendar'[Date]))>=_selectqu-1&&QUARTER(MAX('DIM_Calendar'[Date]))<=_selectqu)
||
AND(YEAR(MAX('DIM_Calendar'[Date])) =_selectyear -1 ,QUARTER(MAX('DIM_Calendar'[Date]))=_selectqu),1,0))
Untill now,all is right.
But I put the site(a column in Fact_sale),the filter is broken
I guess the problem caused by no relationship with the filter measure.
Should I add any relationship in the measure?
Regards,
Joseph