Measure returning correct total but row value is incorrect

[otto-user101]

I have a dataset with multiyear data. See sample below.

Year	Region	Employees	Primary Key
2020	West	Employee1	2020WestEmployee1
2020	East	Employee2	2020EastEmployee2
2020	West	Employee3	2020WestEmployee3
2020	South	Employee4	2020SouthEmployee4
2020	South	Employee5	2020SouthEmployee5
2021	North	Employee6	2021NorthEmployee6
2021	North	Employee7	2021NorthEmployee7
2021	East	Employee8	2021EastEmployee8
2021	West	Employee9	2021WestEmployee9
2021	South	Employee10	2021SouthEmployee10
2022	North	Employee11	2022NorthEmployee11
2022	North	Employee12	2022NorthEmployee12
2022	East	Employee13	2022EastEmployee13
2022	West	Employee14	2022WestEmployee14
2022	South	Employee15	2022SouthEmployee15
2022	West	Employee16	2022WestEmployee16
2023	North	Employee17	2023NorthEmployee17
2023	East	Employee18	2023EastEmployee18
2023	West	Employee19	2023WestEmployee19
2023	South	Employee20	2023SouthEmployee20
2023	West	Employee21	2023WestEmployee21
2024	North	Employee22	2024NorthEmployee22
2024	East	Employee23	2024EastEmployee23
2024	West	Employee24	2024WestEmployee24
2024	South	Employee25	2024SouthEmployee25
2024	East	Employee26	2024EastEmployee26
2024	North	Employee27	2024NorthEmployee27

With this dataset, I have a matrix viz that pulls in Region for Rows; Year in columns; measure (count-historical = distinctcount('data'[primary key]). This matrix is formatted as shown in the table below presenting the count of employees by year/region.

Matrix A

Region	2020	2021	2022	2023	2024
North	0	2	2	1	2
South	2	1	1	1	1
East	1	1	1	1	2
West	2	1	2	2	1
Total	5	5	6	5	6

No issues here. Now, I have another matrix with regions in Rows and a measure in Values. There's a year slicer on this page that allows filtering by a range of years. I would like for the measure to pull in the employee count for the min(year) - e.g., if the slicer is set to 2020-2022, the values for 2020 will populate. The dax for this measure is: 

Range Min =

var _minyear = MIN('data'[Year])

var _minval = IF(

ISBLANK(CALCULATE(COUNT('data'[PrimaryKey]), YEAR('data'[Year])=year(_minyear))), 0,CALCULATE(COUNT('data'[Primary Key]),YEAR('data'[Year])=year(_minyear)))

)

return

_minval

The issue is that when the calculated value is 0, the measure populates a value from an adjacent year and not "0", but the total is correct... Does anyone have insight as to why this is happening?

Region	Min	Max
North	2 (should be 0)	2
South	2	1
East	1	1
West	2	2
Total	5	6

[otto-user101]

Hi @v-weiyan1-msft thanks for your response. The dax you provided for Min (and Max) populates 0 when the count is blank. 

There's something I didn't include in my initial post - the regions are grouped and with the new dax, the subtotals and totals are inaccurate, and I'm looking to achieve what's shown in the table below. 

Region	2020	2021	2022
Group A	2	3	3
North	0	2	2
South	2	1	1
Group B	3	2	3
East	1	1	1
West	2	1	2
Total	5	5	6

Can you assist with this? Much appreciated!

[v-weiyan1-msft]

Hi @otto-user101 ,

Based on the example and description you provided, please try code as below to create measure.

Min = 
VAR _mintyear =
    MINX ( ALLSELECTED('Table'),'Table'[Year] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Primary Key] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Region] = MAX ( 'Table'[Region] )
                && 'Table'[Year] = _mintyear
        )
    )
RETURN
    IF ( _count = BLANK (), 0, _count )

Min_Result = 
IF (
    HASONEVALUE ( 'Table'[Region] ),
    [Min],
    SUMX ( VALUES ( 'Table'[Region] ), [Min] )
)

Max = 
VAR _maxyear =
    MAXX ( ALLSELECTED('Table'),'Table'[Year] )
VAR _count =
    CALCULATE (
        DISTINCTCOUNT ( 'Table'[Primary Key] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Region] = MAX ( 'Table'[Region] )
                && 'Table'[Year] = _maxyear
        )
    )
RETURN
    IF ( _count = BLANK (), 0, _count )

Max_Result = 
IF (
    HASONEVALUE ( 'Table'[Region] ),
    [Max],
    SUMX ( VALUES ( 'Table'[Region] ), [Max] )
)

When the slicer is set to 2020-2022, Result is as below.

Best Regards,
Yulia Yan

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