topic Re: How to get Zero for Blank values in DAX Commands and Tips

How to get Zero for Blank values

Chandrashekar — Mon, 22 Aug 2022 12:08:04 GMT

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

Re: How to get Zero for Blank values

amitchandak — Mon, 22 Aug 2022 15:27:42 GMT

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

Re: How to get Zero for Blank values

Chandrashekar — Tue, 23 Aug 2022 03:41:39 GMT

Hello Amit,

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

Regards,

Chandrashekar B

Re: How to get Zero for Blank values

Chandrashekar — Tue, 23 Aug 2022 07:03:34 GMT

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 =

Regards,

Chandrashekar B

Re: How to get Zero for Blank values

v-cazheng-msft — Thu, 25 Aug 2022 02:51:48 GMT

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

Re: How to get Zero for Blank values

Chandrashekar — Thu, 25 Aug 2022 03:56:10 GMT

Hello,

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

Regards,

Chandrashekar B

Re: How to get Zero for Blank values

Chandrashekar — Sun, 28 Aug 2022 08:05:21 GMT

Hello,

Any help to get zero for blank values?

Regards,

Chandrashekar B

Re: How to get Zero for Blank values

daXtreme — Sun, 28 Aug 2022 15:57:48 GMT

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)

Re: How to get Zero for Blank values

Chandrashekar — Mon, 29 Aug 2022 03:41:42 GMT

Hello,

Thank for the instructions. I will check on it.

Regards,

Chandrashekar B