Return sum of distinct value per category

[DanteL]

Hi, this has been driving me mad for hours.

Year	Index	Male
2020	1	1
2020	1	1
2020	1	1
2020	2	1
2020	2	1
2020	2	1
2020	2	1
2020	3	2
2020	3	2
2020	3	2
2020	3	2
2021	4	3
2021	4	3
2021	5	0
2021	5	0
2021	6	0

I'm trying to write a measure to return the value of Male when Index and Year is selected.

So for e.g. if i select Index 1 and 2020 (Date[Year)]) in the slicers, Male should show value of 1
Index 3, Male = 2

And when nothing is selected in Index, Male should show the total of distinct values.
Example, if 2020 is selected, and nothing selected in Index, then Male = 4 (1+1+2)

Appreciate all the help please ! Thanks !

[v-weiyan1-msft]

Hi @DanteL ,

@ryan_mayu nice method! Thank you, for your quick response and the solution provided.
And based on the sample and description you provided, please try the following steps:
1.You can create Calculated table.

Dim_Year = VALUES('Table'[Year])

Dim_Index = VALUES('Table'[Index])

2. Use the following code to create a measure.

Measure = 
VAR SelectedYear = SELECTEDVALUE('Dim_Year'[Year])
VAR SelectedIndex = SELECTEDVALUE('Dim_Index'[Index])
VAR DistinctIndexMale = 
    SUMMARIZE(
        FILTER('Table', 'Table'[Year] = SelectedYear),
        'Table'[Index],
        "DistinctMale", MAX('Table'[Male])
    )
VAR Result = 
    SUMX(DistinctIndexMale, [DistinctMale])
RETURN
    IF(
        HASONEVALUE('Dim_Index'[Index]),
        MAXX(FILTER('Table','Table'[Year] = SelectedYear && 'Table'[Index] = SelectedIndex),'Table'[Male]),
        Result
    )

The fields of the two slicers are from the corresponding Calculated table.
When you select "2020", "1" in the slicers, Result is as below.

When you select "2020" in the slicer, 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.

[ryan_mayu · Accepted solution]

@DanteL 

maybe you can try group by in the power query

[DanteL]

Thanks. Is there a way to do it with measures instead ?