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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
March Fabric Community Update

Fabric Community Update - March 2024

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

Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors