topic Re: ALLNOBLANKROW behaviour in DAX Commands and Tips

ALLNOBLANKROW behaviour

bhalicki — Thu, 19 Aug 2021 10:07:06 GMT

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.

Re: ALLNOBLANKROW behaviour

Fowmy — Thu, 19 Aug 2021 10:28:58 GMT

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

Re: ALLNOBLANKROW behaviour

bhalicki — Thu, 19 Aug 2021 10:34:21 GMT

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.

Re: ALLNOBLANKROW behaviour

Fowmy — Thu, 19 Aug 2021 10:58:27 GMT

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