Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Amoda
New Member

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

2 REPLIES 2
parry2k
Super User
Super User

@Amoda You need to change this:

 

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

 

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

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. 


Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.