Forum Discussion

Amoda's avatar
Amoda
New Member
2 years ago

[Human Logic Failure] Calcualted Column Excludes Second Round of Blanks in OR Statement

Update: This issue was caused by my mind thinking in arrays and expecting a dynamic array style result when I was asking Power BI for a calculated column. I will leave this post here in case anyone else finds themseleves in one of these condundrums. I will retag this as Human Logic Failure so it is clear where the issue was rooted. 

In short:  A calculated column holds a single truth. Dog or Cat. It cannot assign both Dog and Cat in cases where there is a blank as it is not a demisional array. 

/////////////////////////

I am hitting a wall with a column I am working on and was hoping someone could shed some light on it.

For context, I am creating a caluclated column designed to simplfiy a slicer. For each category, it should count any entries in the field and nulls from another field as the denominator of the category. This way the measure still includes blanks and the end user dose not have to select blanks and the category from the slicer. 

Context: 

Topic Was_CatWas_DogWas_Labled
AnimalCat 1
Animal Dog1
AnimalCat 1
Animal   

 

My measure for percentage works fine (but I include it for context):

 

 

 

 

PercentMeasure = Sum(Table[Was_Labled])/COUNT(Table[Topic])

 

 

 

 


However, when create my calculated column for the slicer: 

 

 

 

 

MeasureSlicer = if((Table[Was_Cat]="Cat" || Table[Was_Labled]=blank()), "Cat",if((Table[Was_Dog]="Dog" || Table[Was_Labled] =blank()),"Dog",""))

 

 

 

 

 

On testing the measure slicer as a slicer, the 'Cat' category shows correctly (50% from my example). It correctly slices the measure as expected. However the second category 'Dog' shows at 100% instead of 25%, indicating that it does not include the blanks. I reversed the statment with the categories the other way around and got the same issue. If 'Was_Dog' is first it gives me 25%, but then 100% for 'Was_Cat'. It seems that blanks are only being included in the 1st OR statment. 

 

This behavior also occoured when I replaced the "||" with "Or()" syntax. 

 

I have also tried it with another data structre where the 'Was_Animal' flags were combined into a single field with Cat, Dog, Blank() as the entries. The same issue occoured. The nulls were only counted towards the 1st OR statement. 


Context for second attempt example: 

Topic NewAnimalFieldWas_Labled
AnimalCat1
AnimalDog1
AnimalCat1
Animal  


Is there a workaournd for this behavior? It is very unexpcted considering the same caluclated column in other systems would not operate this way. I would think each if statment would be evaluated independently. 

Or if there is some obvious issue in my code, please let me know. It is late in the day and it is possible I have stared at it too long. 

 

Thanks. 

Need Help

2 Replies

  • parry2k's avatar
    parry2k
    Super User
    2 years ago

    Amoda You need to change this:

     

    PercentMeasure = Sum(Table[Was_Labled])/CALCULATE(COUNT(Table[Topic]), ALL(Table))

     

     

    • Amoda's avatar
      Amoda
      New Member
      2 years ago

      This does not resolve the issue. The issue persisits.

      However, now with morning and caffiene, I see the error of my ways. I am asking for the impossible. A calculated column holds a single truth. Dog or Cat. It cannot assign both Dog and Cat in cases where there is a blank as it is not a demisional array. I was thinking with array logic, not expicit calculated column logic. I was wanting it to return a result depenent on a calculation at the time of the slicer check, not the other way around. 

      Thank you for taking the time in any case. - I will need to rethink my approach.