topic How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values? in DAX Commands and Tips

How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

JustinDoh1 — Thu, 30 Jan 2025 22:35:48 GMT

I have uploaded my updated (1/30) PBIX file here.

Bottom is original question (1/29/2025):

Previously, I had wrapped one measure called "Average" inside another measure called "decimalFix":

Average = AVERAGEX(

VALUES('Date'[DateFormat]),

CALCULATE(

[ClientD_M1]

)

decimalFix =

if(DISTINCTCOUNT('Date'[DateFormat])=1,

format([Average],"0"),

format([Average],"0.0")

)

which made these possible (number with 1 decimal place as well as 0 decimal :

Due to performace issue of "Average" measure, I modified the "Average" measure to "AverageNew" as below:

AverageNew =

AVERAGEX(

FILTER(

SUMMARIZE(

'Census',

'Date'[DateFormat],

"ClientD_M1", [ClientD_M1]

[ClientD_M1] > 0

[ClientD_M1]

)

Now, I am trying to apply the logic of "decimalFix" to "AverageNew" so that on the new visual (matrix), I would like to have both 0 and 1 decimal places.

For now, each day's number is fine (with 0 decimal), but the 'Average Census' needs to show 1 decimal numbers.

How do I go about doing it?

Update (1/30/2025):

Sorry. I realized that I need to come up with a solution to treat rows with null data (because there is filter to limit data and there are more than 1 hierarchy of data):

Bottom measure would not work for the null data that is not showing in these date period:

decimalFixNew =

if(DISTINCTCOUNT(DimDate1[DateFormat])=1,

format([AverageNew],"0"),

format([AverageNew],"0.0")

)

So, requirement is how to get rid of those rows with blank value?

Thanks.

Re: How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

Jihwan_Kim — Thu, 30 Jan 2025 05:54:32 GMT

Hi,

I am not sure if I understood your question correctly, but please try creating a new measure like below.

decimalFixNew = if(DISTINCTCOUNT('Date'[DateFormat])=1, format([AverageNew],"0"), format([AverageNew],"0.0") )

Re: How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

bhanu_gautam — Thu, 30 Jan 2025 05:57:34 GMT

JustinDoh1 , Use this measure

decimalFixNew =
IF(
DISTINCTCOUNT('Date'[DateFormat]) = 1,
FORMAT([AverageNew], "0"),
FORMAT([AverageNew], "0.0")
)

Re: How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

bhanu_gautam — Thu, 30 Jan 2025 05:59:05 GMT

Jihwan_Kim , I didn't saw your response I was drafting one sorry for that

JustinDoh1 , Go with this both are same

Re: How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

JustinDoh1 — Thu, 30 Jan 2025 22:27:22 GMT

bhanu_gautam Thank you so much for your help again. Sorry. I realized that my requirement has to be changed because initially, I had an issue with dealing with null value, and I thought placing on the measure "AverageNew" would meet the need.

[ClientD_M1] > 0

[ClientD_M1]

And I wanted to ask to just deal with 1 decimal place.

Sorry. I presented the scenario which would not meet the issues on initial stage (having multiple hierarchy) yesterday.

I have updated my content on the top with updated PBIX file attached here.

I realized that I need to come up with a solution to treat rows with null data:

How do I fix so that all of "Insurance" hierarchy would not show up?

Re: How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

JustinDoh1 — Thu, 30 Jan 2025 22:27:58 GMT

Jihwan_Kim

Thank you so much for your help again. Sorry. I realized that my requirement has to be changed because initially, I had an issue with dealing with null value, and I thought placing on the measure "AverageNew" would meet the need.

[ClientD_M1] > 0

[ClientD_M1]

And I wanted to ask to just deal with 1 decimal place.

Sorry. I presented the scenario which would not meet the issues on initial stage (having multiple hierarchy) yesterday.

I have updated my content on the top with updated PBIX file attached here.

I realized that I need to come up with a solution to treat rows with null data:

How do I fix so that all of "Insurance" hierarchy would not show up?

Re: How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

Anonymous — Fri, 31 Jan 2025 08:38:04 GMT

Hi JustinDoh1

Thanks for the reply from Jihwan_Kim and bhanu_gautam .

JustinDoh1 , please try to create a measure as follows.

Measure = IF([decimalFixNew] <> BLANK(), 1, 0)

Put the measure into the visual-level filters, set up show items when the value is 1.

If another user's answer helped you, please mark their answer as the solution. This will help more users who are facing the same or similar difficulties. Thank you!

Best Regards,
Yulia Xu

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

Re: How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

JustinDoh1 — Fri, 31 Jan 2025 18:07:53 GMT

Anonymous

Thank you so much for your help.

Even though it works with a small dataset, when I applied into filter on my regular PBIX file (which has a data from year 2022), the visual crashes.

Is there anyway not to use filter but modify/add the logic on "decimalFixNew" by chance? Sorry for trouble..

I am also curious how I could minimize the size or group dataset.

I also experience that when I try on Bookmark, visual crashes more. I am wondering why?

Thanks!

Re: How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

JustinDoh1 — Mon, 03 Feb 2025 18:14:20 GMT

Anonymous I would like to close this thread because I think what I am experiencing is more of performance issue. But, if you are able to help me to come up with a solution to modify the "decimalFixNew" with additional logic, please let me know. Thank you!

Re: How to apply logic to place 1 and 0 decimal place on numbers & dealing with null values?

JustinDoh1 — Mon, 03 Feb 2025 23:57:01 GMT

Anonymous FYI, I just created a new post related to performance issue. Thanks!