Multiple Slicers from Different DIM TABLES with Multiple ALLSELECTED DAX statements

[Anonymous]

Is there a way to to write ALLSELECTED DAX with multiple slicer values where slicers come from different Dim Tables?

Example:

Slicer 1 has Project Name (DimProject[ProjectName])

Slicer 2 has Project Manager (DimTeam[ProjectManager])

Slicer 3 has Calendar Year & Month (DimDate[CalendarYearAndMonth])

I tried the following: CACULATE(SUM(COST), ALLSELECTED(DimProject[ProjectName]), ALLSELECTED(DimTeam[ProjectManager]), ALLSELECTED(DimDate[CalendarYearAndMonth]) )

It didn't work. Can anyone advise on what is the correct way to filter with multiple slicer values [from different tables] while using ALLSELECTED?

[v-eqin-msft · Accepted solution]

Hi @Anonymous ,

According to my understand, you want to sum the cost based on multiple slicer values ,right?

You could use the following formula:

selectionTimes =
VAR _nn =
    IF (
        ISFILTERED ( 'DimProject'[ProjectName] )
            && MAX ( 'Cost'[ProjectName] ) IN ALLSELECTED ( DimProject[ProjectName] ),
        1,
        0
    )
VAR _mm =
    IF (
        ISFILTERED ( 'DimTeam'[ProjectManager] )
            && MAX ( 'Cost'[ProjectManager] ) IN ALLSELECTED ( DimTeam[ProjectManager] ),
        1,
        0
    )
VAR _dd =
    IF (
        ISFILTERED ( 'DimDate'[CalendarYearAndMonth] )
            && MAX ( 'Cost'[CalendarYearAndMonth] )
                IN ALLSELECTED ( 'DimDate'[CalendarYearAndMonth] ),
        1,
        0
    )
RETURN
    IF ( _mm + _nn + _dd > 0, SUM ( 'Cost'[Cost] ) )

total =
SUMX ( 'Cost', [selectionTimes] )

Here is my pbix file.

Did I answer your question ? Please mark my reply as solution. Thank you very much.

If not, please upload some insensitive data samples and expected output.

Best Regards,

Eyelyn Qin

[amitchandak]

@Anonymous , This seems correct

try if this can work

CACULATE(SUM(COST), ALLSELECTED(DimProject), ALLSELECTED(DimTeam), ALLSELECTED(DimDate) )