Forum Discussion
SOLVED-Multiple variable in a measure returns Blank
Hi Guys,
I have multiple variable in a measure and giving me a blank result.
let say i have 4 variable inside the measure and one of then has a null value or zero.
The formula is to multiply all 4 variable and this is what I applied in my measure to removed the null or blank by adding COALESCE to variable. The problem with this code , if all of then are null or zero returns 1 and converted to 100.00%
Is there any idea or suggestion to simplify or fix this kind of scenario.
Sample:
Measure (Yield %) =
VAR _p1 = DIVIDE(table_measure[Pass], PY_Measures[Pass_Input], 0)
VAR _p2 = DIVIDE(table_measure[Fail], PY_Measures[Fail_Input ], 0)
VAR _p3 = DIVIDE(table_measure[Good], PY_Measures[Good_Input], 0)
VAR _p4 = DIVIDE(table_measure[Bad], PY_Measures[Bad_Input], 0)
RETURN
(COALESCE(_p1,1) * COALESCE(_p2,1) * COALESCE(_p3,1) * COALESCE(_p4,1))
| var | output |
| p1 | 1 |
| p2 | 96.63% |
| p3 | 93.39% |
| p4 | 92.81% |
Desired result
| Measure (Yield%) | 83.75% |
Thank you
5 Replies
- lbendlin
Super User
where did you get the output for the variables from? What is the filter context?
The filter available are only year and month. All data will displayed on the dashboard.
PY_Measures is a folder that store all the measures I created. Here is the sample of measure I created for where the _P1 variable comes from(output)Pass_Input = Calculate(PY_Measures(touched), filter(PY,(PY[Category]='WB') && (PY[type='ALL'))) Pass = Calculate(PY_Measures(touched), filter(PY,(PY[Category]='WB') && (PY[type_category='Pass')))- lbendlin
That is not valid DAX. Did you want to write PY_Measures[touched] ? What is the definition of that measure?
This should be a bracket not a parenthesis. please see the below changes.
Pass_Input = Calculate(PY_Measures[touched], filter(PY,(PY[Category]='WB') && (PY[type]='ALL'))) Pass = Calculate(PY_Measures[touched], filter(PY,(PY[Category]='WB') && (PY[type_category]='Pass'))) Touched = SUMX(FILTER(PY, PY[py_input]=1),[py_input])--- py_input is a column with a default value of 1
