Forum Discussion

Marico's avatar
Marico
Helper IV
1 year ago
Solved

Combining matrix

Hi,   I have the following requirements from the user not sure how many of them are possible.   Model Structure: Source and Target data are coming from two different database tables. There is a ...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Marico ,

     

    According to your description, you want to display the data of the current month and the previous five months, and users can use slicer to change the columns in the matrix. Currently, the built-in slicer does not support the default option. I recommend that you use the preselected slicer. The following is my test for your reference. Since I don't know how your year-end target column is obtained, I only display the data up to the current month.

    Step1. Add preselected slicer. 

    Step2. Custom table and Measure.

    _PreselectedSlicer = DATATABLE(
    "IsDirtySlicer", BOOLEAN, 
    {
        {FALSE()},
        {TRUE()}
    }
)
    Measure 2 = 
VAR _select_date = SELECTEDVALUE('Date'[Month-Year])
VAR _previous =UNION(SELECTCOLUMNS(GENERATESERIES(0,5,1),"test",FORMAT(EDATE(TODAY(),-[Value]),"YYYY-MMM")),ROW("test","CurrentYear"))
RETURN
    IF(_select_date IN _previous,TRUE(),FALSE())

    Step3. The Matrix.

     

    Buttons of preselected slicer.

     

     

     

     

     

    Best regards,

    Mengmeng Li

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Marico ,

 

According to your description, you want to display the data of the current month and the previous five months, and users can use slicer to change the columns in the matrix. Currently, the built-in slicer does not support the default option. I recommend that you use the preselected slicer. The following is my test for your reference. Since I don't know how your year-end target column is obtained, I only display the data up to the current month.

Step1. Add preselected slicer. 

Step2. Custom table and Measure.

_PreselectedSlicer = DATATABLE(
    "IsDirtySlicer", BOOLEAN, 
    {
        {FALSE()},
        {TRUE()}
    }
)
Measure 2 = 
VAR _select_date = SELECTEDVALUE('Date'[Month-Year])
VAR _previous =UNION(SELECTCOLUMNS(GENERATESERIES(0,5,1),"test",FORMAT(EDATE(TODAY(),-[Value]),"YYYY-MMM")),ROW("test","CurrentYear"))
RETURN
    IF(_select_date IN _previous,TRUE(),FALSE())

Step3. The Matrix.

 

Buttons of preselected slicer.

 

 

 

 

 

Best regards,

Mengmeng Li

  • Marico's avatar
    Marico
    Helper IV
    1 year ago

    Hi Anonymous Thank you for your analysis and testing.

     

    Please are a few points from my side:

    1. Can we have the solution a bit dynamic that if we select current month then previous 5 months need not to be selected from the slicer and they are auto populated in the matrix, else every month user will have to manually select current plus previous 5 months from slicer?

    2. The target values are coming from target table in the file shared earlier on GitHub.

    3. It is not possible to accomodate all requirements mentioned in the original post in one matrix then is there any better visualization for this?

     

    Looking forward to your response.

     

    Thank you!