Forum Discussion

damit23183's avatar
damit23183
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago
Solved

Data not coming as expected

Hi,

 

I am trying to create MATRIX table with hierarcy on (Region, Office, Manager) and all other columns will have aggregate (Measure/Calculated Field) value.

 

However, I would like to show all values whether they have aggregate value or not. 

 

Source Table:

 

 

 

 

I am getting this result:

 

 

 

 

 

 

 

 

 

This is the formula i have applied to get correct count:

 

Total Number of Decision = CALCULATE(
COUNT('Table'[ID]),
Filter('Table', NOT(ISBLANK('Table'[Date])))
)

 

Expected Result:

 

 

 

 

 

 

 

 

 

 

 

 

 

I would like to see all Region, Office and Manager whether they have value or not. With the formula i am using i can only see who has value. 

 

It does look like i am making some mistake in formula but not sure. I tried by GROUPBY and ALLEXCEPT also but didn't work.

 

Thanks

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    6 years ago
    Perhaps:

    Total Number of Decision =
    VAR __value = CALCULATE(
    COUNT('Table'[ID]),
    Filter('Table', NOT(ISBLANK('Table'[Date])))
    )
    RETURN
    IF(ISBLANK(__value),0,__value)

3 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    6 years ago
    Perhaps:

    Total Number of Decision =
    VAR __value = CALCULATE(
    COUNT('Table'[ID]),
    Filter('Table', NOT(ISBLANK('Table'[Date])))
    )
    RETURN
    IF(ISBLANK(__value),0,__value)
    • damit23183's avatar
      damit23183
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

      Hi Greg,

       

      Thank you so much it worked perfectly.

       

      Thanks

    • damit23183's avatar
      damit23183
      Icon for Microsoft Employee rankMicrosoft Employee
      6 years ago

       Hi,

       

      Quick question though,

       

      Is there a way I can put blank instead of 0.

       

      I did try with BLANK() instead of 0 but result was filtered out. I am getting expected result with 0 only, however i would like to put blank or empty cell instead of 0.

       

      Thanks