How to get Zero for Blank values

[Chandrashekar]

Hello,

How to get zero for blank values as am getting for first row but not for 4th.

Dax: 

Res/Res1 =

SUMX(
Summarize('TS','TS'[Priority],'TS'[AS]),
CALCULATE (
COUNTROWS('TS'),USERELATIONSHIP('TS'[Date_Closed_SLA],'Calendar'[Date]),
FILTER (
VALUES('TS'), 'TS'[Parent]=Blank() && 'TS'[In Task2] =1 &&
'TS' [Response/Restore]<>Blank() ),
'TS'[Priority]=earlier('TS'[Priority]),
'TS'[Actual stage]=EARLIER('TS'[AS]
)))+0

[v-cazheng-msft]

Hi @Chandrashekar,

You may try to make some change to your formula.

Res/Res1 =
var count_=SUMX(
Summarize('TS','TS'[Priority],'TS'[AS]),
CALCULATE (
COUNTROWS('TS'),USERELATIONSHIP('TS'[Date_Closed_SLA],'Calendar'[Date]),
FILTER (
VALUES('TS'), 'TS'[Parent]=Blank() && 'TS'[In Task2] =1 &&
'TS' [Response/Restore]<>Blank() ),
'TS'[Priority]=earlier('TS'[Priority]),
'TS'[Actual stage]=EARLIER('TS'[AS]
)))+0
return if(isblank(count_),0,count_)

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!

Best Regards,

Community Support Team _ Caiyun

[Chandrashekar]

Hello, 

I tried this solution(with your formula) but still am getting zero. is there any other way to fix it.

Regards,

Chandrashekar B 

[Chandrashekar]

Hello,

Any help to get zero for blank values?

Regards,

Chandrashekar B

[Chandrashekar]

Hello,

May I know for why zero getting 1-Critical but not getting for 4-low. 

How to get zero for blank values as am getting for first row but not for 4th.

Dax: 

Res/Res1 =

SUMX(
Summarize('TS','TS'[Priority],'TS'[AS]),
CALCULATE (
COUNTROWS('TS'),USERELATIONSHIP('TS'[Date_Closed_SLA],'Calendar'[Date]),
FILTER (
VALUES('TS'), 'TS'[Parent]=Blank() && 'TS'[In Task2] =1 &&
'TS' [Response/Restore]<>Blank() ),
'TS'[Priority]=earlier('TS'[Priority]),
'TS'[Actual stage]=EARLIER('TS'[AS]
)))+0

Regards,

Chandrashekar B

[daXtreme · Accepted solution]

This is because you are using a one-table model. Such models are inherently incorrect and should never be used unless you want to have issues you will never be able to explain (due to, for instance, the auto-exists feature).

Please use a correct model that's a star-schema and your worries will be gone. You definitely should read this article before you even start thinking about data modeling in PBI: Understand star schema and the importance for Power BI … (bing.com)

[Chandrashekar]

Hello,

Thank for the instructions. I will check on it.

Regards,

Chandrashekar B

[amitchandak]

@Chandrashekar , is Priority coming from a separate dimension table joine diwth main fact. If not try that out

[Chandrashekar]

Hello Amit,

Can you please show how to do it as am bit new to this.

Regards,

Chandrashekar B