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.

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.