Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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!!
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
51 | |
27 | |
24 | |
13 | |
8 |
User | Count |
---|---|
74 | |
56 | |
47 | |
16 | |
12 |