Forum Discussion

DuncanYeah's avatar
DuncanYeah
Icon for Helper III rankHelper III
1 year ago
Solved

Show Zero in table

Hi everyone,   I am trying to get my table to display categories with number count zero. I had tried to use +0 but after appling page filter, the table showed some categories that not match the pa...
Need Help
Show and Tell
Tips and Tricks
  • DuncanYeah's avatar
    DuncanYeah
    1 year ago

    Hi Anonymous 

    Thx for your reply.

    I change the code a little bit and it works.

    Code:

    Sum_income = 
var x = SUM(I_table[Income])
var _P_CatList = CALCULATETABLE(VALUES(P_table[P_Cat]),FILTER(I_table,I_table[Project type] IN VALUES(I_table[Project type])))
return 

IF(MAX(P_table[P_Cat]) in _P_CatList,x,x+0
)

     Thank You.

     

    Best Regards,

    Duncan

     

DuncanYeah's avatar
DuncanYeah
Icon for Helper III rankHelper III
1 year ago

Hi Ritaf1983 ,

Thank you for your reply.

I can do that in my dummy data but in real situation I am not allow to change the data from PQ.

 

Regards,

Duncan

Ritaf1983's avatar
Ritaf1983
Icon for Super User rankSuper User
1 year ago

Hi DuncanYeah 
You can use this DAX :

Sum_income =
var
t1 =SUMMARIZE( CALCULATETABLE('I_table',I_table[Income]<> BLANK()),I_table[I_NO],I_table[B_type],I_table[Project type],"sum1",SUM(I_table[Income]))
var
 t2 =SUMMARIZE( CALCULATETABLE('I_table',I_table[Income] = BLANK()),I_table[I_NO],I_table[B_type],I_table[Project type],"sum2",SUM(I_table[Income])+0)
 RETURN
SUMX(t1,[sum1])+SUMX(t2,[sum2])

The updated pbix is attached

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

  • DuncanYeah's avatar
    DuncanYeah
    Icon for Helper III rankHelper III
    1 year ago

    Hi Ritaf1983 ,

    Thx for reply.

    It works if the filter parameters are all in the same table.However, in the real case the filters parameter are from three different table.

     

    Thank you for your afford and patient.

     

    Regards,

    Duncan

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi DuncanYeah ,

     

    You can try this code to create a measure.

    Sum_income = 
var x = SUM(I_table[Income])
var _P_CatList = CALCULATETABLE(VALUES(P_table[P_Cat]),FILTER(I_table,I_table[Project type] IN VALUES(I_table[Project type])))
return 

IF(MAX(P_table[P_Cat]) in _P_CatList,x+0,x)

    Result is as below.

     

    Best Regards,
    Rico Zhou

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • DuncanYeah's avatar
    DuncanYeah
    Icon for Helper III rankHelper III
    1 year ago

    Hi Anonymous 

    Thx for your reply.

    I change the code a little bit and it works.

    Code:

    Sum_income = 
var x = SUM(I_table[Income])
var _P_CatList = CALCULATETABLE(VALUES(P_table[P_Cat]),FILTER(I_table,I_table[Project type] IN VALUES(I_table[Project type])))
return 

IF(MAX(P_table[P_Cat]) in _P_CatList,x,x+0
)

     Thank You.

     

    Best Regards,

    Duncan