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
bhalicki
Helper II
Helper II

ALLNOBLANKROW behaviour

Hi all,

 

ALLNOBLANKROW isn't giving me the results I expect, i'm trying to understand why...

My model is as follows:

PROJECT

bhalicki_3-1629367406191.png

BUDGET

bhalicki_2-1629367386770.png

 

Project and Budget are related by ProjectCode (1 to many).  Budget has a project (12340) which is not in related table Project.

 

I have a measure:

TotalBudgetNoBlanks = CALCULATE(SUM(Budget[Amount]), ALLNOBLANKROW(Budget[ProjectCode]))
which gives me:
bhalicki_0-1629367188398.png

I was expecting the blank row to be removed.

 

When I add the project description to the table and modify the measure as such, it seems to do what I want:

 TotalBudgetNoBlanks = CALCULATE(SUM(Budget[Amount]), ALLNOBLANKROW(Project[ProjectCode]))

bhalicki_4-1629367515529.png
Hoping someone could please explain why adding the description has this effect? I want to get blank row removed, using ALLNOBLANKROW and just the ProjectCode.
 
Thanks in advance,

Ben.
2 ACCEPTED SOLUTIONS
Fowmy
Super User
Super User

@bhalicki 

You can modify your measure as follows. You are applying a filter from the Project table which will eliminate any value that is not available in the project table.

 TotalBudgetNoBlanks = CALCULATE(SUM(Budget[Amount]), Project))

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

@bhalicki 

In your calculation, when you add Description, it adds a filter eventually filtering the values. I added Product as a filter to Calculate the force the only available Project Codes.


I will direct you to some good resources to understand further:

https://www.youtube.com/watch?v=GIP5dzBmUxc


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

View solution in original post

3 REPLIES 3
Fowmy
Super User
Super User

@bhalicki 

You can modify your measure as follows. You are applying a filter from the Project table which will eliminate any value that is not available in the project table.

 TotalBudgetNoBlanks = CALCULATE(SUM(Budget[Amount]), Project))

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

@Fowmythat does exactly what i'm after, thank you very much!  My only question is, are you able to explain why ALLNOBLANKROW is doing what it is in my scenario above?  Based on documentation and tutorials, seems this is exactly what it is designed to do.

@bhalicki 

In your calculation, when you add Description, it adds a filter eventually filtering the values. I added Product as a filter to Calculate the force the only available Project Codes.


I will direct you to some good resources to understand further:

https://www.youtube.com/watch?v=GIP5dzBmUxc


Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.