Forum Discussion

JosephWu's avatar
JosephWu
Frequent Visitor
3 years ago
Solved

Slicer Setting limit the matrix?

Hi All,

I have touched the power BI almost 1month,and it's really funny and stimulate creativity by the DAX and matrix combined.

 

Recently,I just meet a problem,and solved that by YT Video and Blog.

A Business solution,
I need to show [Order].[Unit].[Revenue], filter by row[site] and column [Year].[Half Year].[Quarter].[Month]
If I select the 2022 Q3,my matrix will show 2022.Q3 /2022.Q2 and 2021/Q3 (this Quarter/Last Quarter/Last year with same Quarter)
and the 2022.Q3 table including QoQ YoY value 
(it could not show the other Quarter like 2022 Q1)
the final like this:

(Desktop)                                                                              (Report)

 

 

My slicer is YQ , created by Column with measure = year*10+Quarter([Date]) , type is whole number

and my value measure bulit by below with the swicth function 


 

 

 

 

 

 

 

 

Fortunately,I got right answer,and this way also solved cross year problem(like 2022 Q1 /2021 Q4/ 2021 Q1)

 

But,I want to know! Is there ideal with dropdown slicer to solve this solution like below??

 

 

 


 And,

Could I select the year slicer(2022) and Quarter slicer(Q3), the matrix will show 2021-Q3 and 2022-Q2/Q3 data?? 
Except for createing two calender table,Is there any way to do that?

Many question,
thanks for watch,
and I will appreciate the reply!

Need Help
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    3 years ago

    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

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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

    • JosephWu's avatar
      JosephWu
      Frequent Visitor
      3 years ago

      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