cancel
Showing results for
Did you mean:

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Frequent Visitor

## Measure to show zero data where no data for date

Hi,

We are struggling with DAX, to create a measure that puts zeros for blank values.

Data:

Date Time                   Tokens   User

 09/11/2020 00:00:01 10 Harry 09/11/2020 01:00:01 10 Harry 09/11/2020 02:00:01 10 Harry 09/11/2020 03:00:01 10 Harry 09/11/2020 04:00:01 10 Harry 09/11/2020 05:00:01 10 Harry 09/11/2020 06:00:01 10 Harry 09/11/2020 07:00:01 10 Harry 09/11/2020 08:00:01 10 Harry 09/11/2020 09:00:01 10 Harry 09/11/2020 09:00:01 10 Sally 09/11/2020 10:00:01 10 Harry 09/11/2020 10:00:01 10 Sally 09/11/2020 11:00:01 10 Harry 09/11/2020 11:00:01 10 Sally 09/11/2020 11:00:01 20 Bob 09/11/2020 12:00:01 10 Harry 09/11/2020 12:00:01 10 Sally 09/11/2020 12:00:01 20 Bob 09/11/2020 13:00:01 10 Harry 09/11/2020 13:00:01 10 Sally 09/11/2020 14:00:01 10 Harry 09/11/2020 14:00:01 10 Sally 09/11/2020 15:00:01 10 Harry 09/11/2020 16:00:01 10 Harry 09/11/2020 17:00:01 10 Harry 09/11/2020 18:00:01 10 Harry 09/11/2020 19:00:01 10 Harry 09/11/2020 20:00:01 10 Harry 09/11/2020 21:00:01 10 Harry 09/11/2020 22:00:01 10 Harry 09/11/2020 23:00:01 10 Harry 10/11/2020 00:00:01 10 Harry

Visual initially looks good,

but when we filter by user.

Line should go to zero at 18:00 on 09/11/2020.

We think a measure should resolve this.

We've tried various ideas i.e.

Measure = IF((CALCULATE(SUM(Sheet3[Tokens])) < 1), 0,(CALCULATE(SUM(Sheet3[Tokens]))))

But have not succeded in the correct expression.
Happy to share pbix. if it makes it clearer.

1 ACCEPTED SOLUTION
Super User

@Cgull this is a large problem than what looks on the surface, as a best practice, you always want to have dimension in your model and strive for star schema and that's is the root cause of this problem. Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

See attached and now you want to tweak the measure as referred to in the previous reply for the SQLBI video.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

5 REPLIES 5
Super User

@Cgull this is a large problem than what looks on the surface, as a best practice, you always want to have dimension in your model and strive for star schema and that's is the root cause of this problem. Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

See attached and now you want to tweak the measure as referred to in the previous reply for the SQLBI video.

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Super User

@Cgull I'm sure missing something here, can you share pbix file.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Hi @parry2k
Hopefully this works?

Best wishes

Super User

@Cgull one quick way is to add  +0 to the measure, and also check this post that explains it very well. How to return 0 instead of BLANK in DAX - SQLBI

Check my latest blog post Comparing Selected Client With Other Top N Clients | PeryTUS  I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Frequent Visitor

Hi @parry2k, we did have a go and try.

Measure = SUM(Sheet3[Tokens]) + 0
But we must be misunderstanding the DAX, as we don't see any zeros.

Best wishes

Announcements

#### Fabric certifications survey

Certification feedback opportunity for the community.

#### Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

#### Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors