Forum Discussion
Helper IIMeasure breaks relationship in table
Hi,
I am having an issue with by measures breaking the relationship between Analyte Name and Contaminant
The table below is wokring but I have a constant (2000) in my Ct calculation Ct = CALCULATE(IF([s]>0,([V]-(2000-1.78*[s]))/[s],IF([V]>2000,7,0))) that I have to replace with a lookup from another table.
I have my lookup table below with a single direction one to many relationship between Contaminant and Analyte Name
The table below shows the new columns Contaminant and Grade C. The relationship here is working
If I add my Ct calculation the relationship breaks as per table below.
I need to modify my Ct calulation to somthing like Ct = CALCULATE(IF([s]>0,([V]-(MAX('Contaminant Threshholds'[Grade C])-1.78*[s]))/[s],IF([V]>MAX('Contaminant Threshholds'[Grade C]),7,0))).
Any assistance would be gratly appreciated.
Thanks Rodney
5 Replies
- d_gosbell
Super User
It could be that the relationship is pointing in the wrong direction. Normally for a lookup table I would expect that Contaminant would be on the 1 side of the relationship. Then you would not need to do MAX('Contaminant Threshholds'[Grade C]) you could just do RELATED('Contaminant Threshholds'[Grade C]).
If you swap the "many to 1" to a "1 to many" (or vise versa) setting does that fix this issue?
zentonNo I have tried every possible combination
Thanks
- d_gosbell
Oh, I see what it is. You have a hard coded 0 in the final "else" clause which forces a non-blank value for every possible combination of contaminant.
Ct = CALCULATE(IF([s]>0,([V]-(MAX('Contaminant Threshholds'[Grade C])-1.78*[s]))/[s],IF([V]>MAX('Contaminant Threshholds'[Grade C]),7,0)))
If you just remove that it will return a blank instead of 0 and by default power BI will not display rows where all the measures evaluate to blank
eg
Ct = CALCULATE(IF([s]>0,([V]-(MAX('Contaminant Threshholds'[Grade C])-1.78*[s]))/[s],IF([V]>MAX('Contaminant Threshholds'[Grade C]),7)))
v-yulgu-msftMicrosoft Employee
Hi zenton ,
Does d_gosbell's suggestion that remove value 0 in the final "else" clause work in your scenario? If so, please kindly mark the helpful reply as an answer so that it can benefit more users.
Best regards,
Yuliana Gu
