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.
I am having a problem with DAX formula.
I have two tables with following relations
EVS ALL_IDS
ID *:1 ID
value
I want to show a table visualization with ID (All_IDs) and a following measures based on EVS table:
average(value)
stdev.s(value)
custom measure named AA_ID
AA_ID =
IF (
[Average_ID] < 88.5,
( 88.5 - [Average_ID] ) + 1.4 * [STDEV_ID],
IF (
[Average_ID] > 91.5,
( [Average_ID] - 91.5 ) + 1.4 * [STDEV_ID],
[STDEV_ID] * 1.4
)
)
Measure AA_ID calculates for all the IDs in EVS table but also for the IDs (ALL_IDS) that are not present in EVS table which is unwanted behavior. Value 88.5 is shown for those IDs. I figured out that DAX take 0 as average and stdev in those cases instead of null and still calculates AA_ID (which is 88.5 in case of other two measures being 0).
How can I filter those out (I want the calculation to be performed only for my EVS table). At the same time (because of other graphs) I still want to be able to filter by ID (All_IDs).
Solved! Go to Solution.
I found a solution, a very ugly one so if anybody has better idea I would aprreicate it.
I defined a new measure:
IF ([AA_ID] = 88.5, blank (), [AA_ID])
@davidz106 . Try like
Calculate( IF (
[Average_ID] < 88.5,
( 88.5 - [Average_ID] ) + 1.4 * [STDEV_ID],
IF (
[Average_ID] > 91.5,
( [Average_ID] - 91.5 ) + 1.4 * [STDEV_ID],
[STDEV_ID] * 1.4
)
), not(isblank(EVS([ID])) )
I found a solution, a very ugly one so if anybody has better idea I would aprreicate it.
I defined a new measure:
IF ([AA_ID] = 88.5, blank (), [AA_ID])
This does not seem to work. Nothing changes if I use:
AA_ID =
CALCULATE (
IF (
[Average_ID] < 88.5,
( 88.5 - [Average_ID] ) + 1.4 * [STDEV_ID],
IF (
[Average_ID] > 91.5,
( [Average_ID] - 91.5 ) + 1.4 * [STDEV_ID],
[STDEV_ID] * 1.4
)
),
NOT ( ISBLANK ( EVS[ID] ) )
)
or EVS[value] for that matter. I also tried EVS[value]>0 in second to last row with no success.
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 |
---|---|
107 | |
99 | |
76 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |