ALLNOBLANKROW behaviour

[bhalicki]

Hi all,

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

My model is as follows:

PROJECT

BUDGET

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:

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

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.

[Fowmy · Accepted solution]

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

[Fowmy · Accepted solution]

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

[bhalicki]

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

[Fowmy · Accepted solution]

@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