When selecting several options on slicer, display specific target value

[gdonlo]

Hi there, 

I have a target table which establishes a yearly target for each division in the company.

I am trying to create a measure that does the following: when more than one value is selected on slicer containing Division (or no values are selected), apply the target value for International Division. When only one division is selected, the target for the division selected should be displayed. 

Alternatively, I would be happy to be able to set the target as a specific number when more than one division is selected or there is no selection. But DAX should still pick up the target for each division from the data model when only one division is selected.

I have tried different things but I am quite frankly stuck. 

Thanks in advance for any help!

[gdonlo]

Hi @v-binbinyu-msft, 

Thank you so much for your reply. This is a brilliant solution and works great, but I do need a bit more help.

Imagine instead of the table you suggest, there is a different target for 2021 that also needs to be taken into account. My table would be something like this: 

Division	Year	Target
International	2021	4
International	2022	3
Europe	2021	4
Europe	2022	1.6
Other	2021	3
Other	2022	5

When no values are selected for Division or when more than one is selected on the slicer, I would need to display 4 for 2021 and 3 for 2022 (or, what is the same, the value for the International division). 

Thanks again!!

[v-binbinyu-msft · Accepted solution]

Hi @gdonlo ,

Please try below DAX formula:

Measure =
VAR cur_division =
    SELECTEDVALUE ( 'Table'[Division] )
VAR ctn =
    HASONEVALUE ( 'Table'[Division] )
VAR target_for_2021 =
    CALCULATE (
        MAX ( 'Table'[Target] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Division] = "International"
                && 'Table'[Year] = 2021
        )
    )
VAR target_for_2022 =
    CALCULATE (
        MAX ( 'Table'[Target] ),
        FILTER (
            ALL ( 'Table' ),
            'Table'[Division] = "International"
                && 'Table'[Year] = 2022
        )
    )
VAR cur_target =
    CALCULATE ( SUM ( 'Table'[Target] ), 'Table'[Division] = cur_division )
RETURN
    IF (
        ctn,
        cur_target,
        "2021:" & target_for_2021 & "
" & "2022:" & target_for_2022
    )

Please refer attached .pbix file.

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-binbinyu-msft]

Hi @gdonlo ,

Please try below :

My test  table:

Table:

Create a measure:

Measure =
VAR cur_division =
    SELECTEDVALUE ( 'Table'[Division] )
VAR ctn =
    HASONEVALUE ( 'Table'[Division] )
VAR cur_value =
    CALCULATE ( MAX ( 'Table'[Yearly Target] ), 'Table'[Division] = cur_division )
RETURN
    IF ( ctn, cur_value, 80000 )

Please refer attached .pbix file

Best regards,
Community Support Team_ Binbin Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.