Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi all,
I'm loking for some help with adding in filters to this dax measure:
Score % difference from Target =
Solved! Go to Solution.
If you want to have the filter directly in the DAX measure please try
Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
RETURN
IF(
NOT ISBLANK([Score]),
CALCULATE(
DIVIDE(
[Score] - __SUM,
__SUM
),
TABLE[COLUMN]="Value"
)
)
OR
Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
RETURN
IF(
NOT ISBLANK([Score]),
CALCULATE(
DIVIDE(
[Score] - __SUM,
__SUM
),
FILTER(
ALL(TABLE),
TABLE[COLUMN]="Value"
)
)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
first of all due to performance you should rewrite the formula like this to save caluclation time:
Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
RETURN
IF(
NOT ISBLANK([Score]),
DIVIDE(
[Score] - __SUM,
__SUM
)
)
Regarding the filter in general there are 100 ways for filtering.
you can filte rin the measure using CALCULATE function. But as you describe it yoi should use a slicer or a visual filter
Slicers in Power BI - Power BI | Microsoft Learn
Format filters in Power BI reports - Power BI | Microsoft Learn
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
If you want to have the filter directly in the DAX measure please try
Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
RETURN
IF(
NOT ISBLANK([Score]),
CALCULATE(
DIVIDE(
[Score] - __SUM,
__SUM
),
TABLE[COLUMN]="Value"
)
)
OR
Score % difference from Target =
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
RETURN
IF(
NOT ISBLANK([Score]),
CALCULATE(
DIVIDE(
[Score] - __SUM,
__SUM
),
FILTER(
ALL(TABLE),
TABLE[COLUMN]="Value"
)
)
)
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
I'll change over the Dax to the more efficent one.
I need to filter to be in the DAX as this is for having dynamic values and I havn't seen a way to add a filter to these values so having a measure for each region seems to be the necesary soloution.
Thankyou for your help!
I think I made a mistake.
The measure has to be like this:
Score % difference from Target =
IF(
NOT ISBLANK([Score]),
CALCULATE(
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
RETURN
DIVIDE(
[Score] - __SUM,
__SUM
),
TABLE[COLUMN]="Value"
)
)
or this
Score % difference from Target =
IF(
NOT ISBLANK([Score]),
CALCULATE(
VAR __SUM = SUM('Targets'[Ajusted Target (per million)])
RETURN
DIVIDE(
[Score] - __SUM,
__SUM
),
FILTER(
ALL(TABLE),
TABLE[COLUMN]="Value"
)
)
)
Can you please try and verify?
Best regards
Michael
-----------------------------------------------------
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Appreciate your thumbs up!
@ me in replies or I'll lose your thread.
I've used the second one from your revised ones and its all working, thankyou for your help!
Great! Thakn you for the feedbck! 🙂
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
110 | |
102 | |
99 | |
38 | |
37 |
User | Count |
---|---|
158 | |
125 | |
76 | |
74 | |
63 |