Forum Discussion

bhalicki's avatar
bhalicki
Helper V
5 years ago
Solved

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

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.
DAX
PowerBI Community
  • Fowmy's avatar
    Fowmy
    5 years ago

    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's avatar
    Fowmy
    5 years ago

    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


3 Replies

  • Fowmy's avatar
    Fowmy
    Super User
    5 years ago

    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's avatar
      bhalicki
      Helper V
      5 years ago

      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's avatar
        Fowmy
        Super User
        5 years ago

        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