How to add slicer value to filter in Union & Row Function dataset

[chintusasmal123]

I'm using this formula to show record in Matrix Visual 

UNION(

    ROW("Quarters","Last Quater","Number",CALCULATE(COUNT(HRSSNew[ID]),FILTER(HRSSNew,HRSSNew[Year]=YEAR(UTCTODAY())-1 && HRSSNew[QuarterNo] = 4 && HRSSNew[ProcessType] = "Reports"))),

    ROW("Quarters","Quater 1","Number",CALCULATE(COUNT(HRSSNew[ID]),FILTER(HRSSNew,HRSSNew[Year]=YEAR(TODAY()) && HRSSNew[QuarterNo] = 1 && HRSSNew[ProcessType] = "Reports"))),

    ROW("Quarters","Quater 2","Number",CALCULATE(COUNT(HRSSNew[ID]),FILTER(HRSSNew,HRSSNew[Year]=YEAR(TODAY()) && HRSSNew[QuarterNo] = 2 &&HRSSNew[ProcessType] = "Reports"))),

            ROW("Quarters","Quater 3","Number",CALCULATE(COUNT(HRSSNew[ID]),FILTER(HRSSNew,HRSSNew[Year]=YEAR(TODAY()) && HRSSNew[QuarterNo] = 3 && HRSSNew[ProcessType] = "Reports"))),

            ROW("Quarters","Quater 4","Number",CALCULATE(COUNT(HRSSNew[ID]),FILTER(HRSSNew,HRSSNew[Year]=YEAR(TODAY()) && HRSSNew[QuarterNo] = 4 && HRSSNew[ProcessType] = "Reports"))      

            ))

This is static value but user want to filter the record based on Year Slicer Selected ?
Also they want the default slicer to be currect year.
Anyone who has done this before ?

[Anonymous · Accepted solution]

Hi @chintusasmal123 ,

It appears that you are trying to create a calculated table. As a calculated table, its values will not be affected by user interactions such as slicers and filters. Please follow the steps outlined below to get the expected table:

1. Create a year dimension table (Do not create any relationship with the table 'HRSSNew')

2. Create a slicer which apply the field [Year] of the above dimension table

3. Create a measure as below 

Number = 
VAR _year =
    SELECTEDVALUE ( 'Year'[Year] ,YEAR(TODAY()))
VAR _count =
    CALCULATE (
        COUNT ( HRSSNew[ID] ),
        FILTER (
            HRSSNew,
            ( HRSSNew[Year] = _year
                || ( HRSSNew[Year] = _year - 1
                && HRSSNew[QuarterNo] = 4 ) )
                && HRSSNew[ProcessType] = "Reports"
        )
    )
RETURN
    _count

Best Regards