Measure breaks relationship in table

[zenton]

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

[v-yulgu-msft]

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

[d_gosbell]

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?

[zenton]

No I have tried every possible combination

Thanks

[Anonymous]

Were you able to solve the problem? I am encountering a problem which appears to be similar to what you have reported. My measure is a very simple one .Just  a constant value.  The moment I add this to the drill down table visual , the table stops regarding the 1-many relationship and brings in all records.

https://community.powerbi.com/t5/Desktop/Adding-a-simple-constant-valued-measure-stops-breaks/m-p/22...

[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)))