Forum Discussion

lkshck's avatar
lkshck
Icon for Helper III rankHelper III
4 years ago
Solved

Simulate null values for missing data

Hey, I have a table which contains a date (by day), a clientId and a count (number of requests). There are some clients which doesn't make requests for some days which leads to no data in this table...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi lkshck ,

     

    I suggest you to create a DimDate table by CALENDAR() or CALENDARAUTO() function. 

    Date = 
CALENDAR(MIN('Table'[date]),MAX('Table'[date]))

    Relationship:

    Measure:

    Measure = CALCULATE(SUM('Table'[count]))+0

    Use [Date] from DimDate table to create your visuals. 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.

Syndicate_Admin's avatar
Syndicate_Admin
Icon for Administrator rankAdministrator
4 years ago

What you are looking for is to add a 0 as a daily goal? or is what I understood in your question! can you give an example with an excel graph of what you are looking for to help you?

lkshck's avatar
lkshck
Icon for Helper III rankHelper III
4 years ago

0 should be added to every day where the client made any request. So the current table looks like the following:

 

So you can see that the client 9-8765-4321 doesn't made any calls on 02.06.2022. Goal is to add this clientId as well to this date, but with count = 0. So the expected table should look like:

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi lkshck ,

     

    I suggest you to create a DimDate table by CALENDAR() or CALENDARAUTO() function. 

    Date = 
CALENDAR(MIN('Table'[date]),MAX('Table'[date]))

    Relationship:

    Measure:

    Measure = CALCULATE(SUM('Table'[count]))+0

    Use [Date] from DimDate table to create your visuals. 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.