Matrix visual should display default periods along with selected period from the PeriodNew Slicer.

[V_Ramugade]

Hi,

I am working on one of the dashboard where my requirement is stated as below.

I have a table with period field and periodnew field which is a grouping of period fields. Now, I want a slicer for PeriodNew which shoul be work as mentioned below.


CASE 1:- When I Select "00" option from the PeriodNew Slicer then my matrix visual should display me Period "00" and all default periods into the column section of the matrix visual like shown in below image.

CASE 2:- When I Select "97" option from the PeriodNew Slicer then my matrix visual should display me Period "97" and all default periods into the column section of the matrix visual.
CASE 3:- When I Select "98" option from the PeriodNew Slicer then my matrix visual should display me Period "98" and all default periods into the column section of the matrix visual.
CASE 4:- When I Select "99" option from the PeriodNew Slicer then my matrix visual should display me Period "99" and all default periods into the column section of the matrix visual.
CASE 5:- When I Select "Reg" option from the PeriodNew Slicer then my matrix visual should display me Period "Reg" and all default periods into the column section of the matrix visual.

CASE 6:- When I Select "Default" option from the PeriodNew Slicer then my matrix visual should display me all default periods into the column section of the matrix visual.

[Anonymous]

Hi @V_Ramugade 

Thanks for the reply from amitchandak .

V_Ramugade , the following test is for your reference.

1. Create a calculated table as the slicer

Slicer = VALUES('Table'[PeriodNew])

There is no relationship between the two tables.

2. Create a measure as follows

SelectedPeriods = 
VAR SelectedPeriodNew =
    SELECTEDVALUE ( 'Slicer'[PeriodNew] )
RETURN
    IF (
        SelectedPeriodNew = "Default",
        IF ( MAX ( 'Table'[PeriodNew] ) = "Default", 1 ),
        IF (
            MAX ( 'Table'[PeriodNew] ) = SelectedPeriodNew
                || MAX ( 'Table'[PeriodNew] ) = "Default",
            1
        )
    )

Put the measure into the visual-level filters, set up show items when the value is 1.

Output:

Best Regards,
Yulia Xu

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[V_Ramugade]

Hi @Anonymous ,

Thanks for your valuable time you gave for the solution. But I already used this method and this method is workable with only 2 dataset i.e. your primary dataset Period & secondary that you create through calculate table DAX. 

But once you add any other dataset field into the Matrix visual then slicer stop working. Obiviously this is because of missing relatioship or something else which prevent it to work as expected.

Hence I had modify the PBIX by adding the Sales table and used the product column into the row field of the matrix visual then you can see filter stop working.

Thanks & Regards,

Vishal Ramugade.

[amitchandak]

@V_Ramugade , One way is you duplicate the table and keep it disconnected and based on the selection you pass other values to measure based on selection

You can create a measure like

SelectedPeriodsMeasure = 
VAR SelectedPeriod = SELECTEDVALUE(PeriodNew[PeriodNew], "Default")  -- Get selected value from the slicer, defaulting to "Default"
VAR DefaultPeriods = {"00", "97", "98", "99", "Reg"}  -- Set of default periods
VAR CurrentPeriod = [YourPeriodField]  -- Replace with your actual period field

RETURN
SWITCH(
    TRUE(),
    SelectedPeriod = "Default", 
        IF(CurrentPeriod IN DefaultPeriods, 1, BLANK()),  -- Only show default periods
    SelectedPeriod = "00", 
        IF(CurrentPeriod = "00" || CurrentPeriod IN DefaultPeriods, 1, BLANK()),  -- Show "00" and default periods
    SelectedPeriod = "97", 
        IF(CurrentPeriod = "97" || CurrentPeriod IN DefaultPeriods, 1, BLANK()),  -- Show "97" and default periods
    SelectedPeriod = "98", 
        IF(CurrentPeriod = "98" || CurrentPeriod IN DefaultPeriods, 1, BLANK()),  -- Show "98" and default periods
    SelectedPeriod = "99", 
        IF(CurrentPeriod = "99" || CurrentPeriod IN DefaultPeriods, 1, BLANK()),  -- Show "99" and default periods
    SelectedPeriod = "Reg", 
        IF(CurrentPeriod = "Reg" || CurrentPeriod IN DefaultPeriods, 1, BLANK()),  -- Show "Reg" and default periods
    1  -- Default case, show all periods if no match
)

One more way to solve

Single Select with Select All : https://youtu.be/plGqCEZRfXU