Forum Discussion

Alberto76's avatar
Alberto76
Regular Visitor
9 years ago
Solved

line chart not representing dates without data

Hi all, I've searched through the posts and around internet but I didn't found a similar problem like mine solved. I have a DB containing IDs of services and related quantities over months and year...
  • v-yulgu-msft's avatar
    v-yulgu-msft
    9 years ago

    Hi Alberto76,

     

    If there are no data records in a month,  the line chart will not represent dots for those month. In my test, I created a calendar table and crossjoin it with the DB table. However, I think this workaround is not appropriate to the dataset which contains very large records.

     

    In this crossjoin table, I added a calculated column:

    Column = IF('Table 5'[Year_Month]='Table 5'[Date],'Table 5'[Qty],'Table 5'[QTY2])

     

    New another calculate table only contains ID, Year_Month and Qty columns:

    Table 6 = SELECTCOLUMNS('Table 5',"ID",'Table 5'[ID],"Date",'Table 5'[Date],"QTY",'Table 5'[Column])

    Then, I put the running total measure in Value, the dates column in Axis. I got the below output, the line chart represents a continuous line with dots for everyday.

     

    Thanks,
    Yuliana Gu

     

     

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi Alberto76,

 

If there are no data records in a month,  the line chart will not represent dots for those month. In my test, I created a calendar table and crossjoin it with the DB table. However, I think this workaround is not appropriate to the dataset which contains very large records.

 

In this crossjoin table, I added a calculated column:

Column = IF('Table 5'[Year_Month]='Table 5'[Date],'Table 5'[Qty],'Table 5'[QTY2])

 

New another calculate table only contains ID, Year_Month and Qty columns:

Table 6 = SELECTCOLUMNS('Table 5',"ID",'Table 5'[ID],"Date",'Table 5'[Date],"QTY",'Table 5'[Column])

Then, I put the running total measure in Value, the dates column in Axis. I got the below output, the line chart represents a continuous line with dots for everyday.

 

Thanks,
Yuliana Gu

 

 

Alberto76's avatar
Alberto76
Regular Visitor
9 years ago

Hi Yuliana,

sorry for my late feedback but I had some emergency to menage...

 

I conceptually understood the logic you applied, but, as I'm a newby of Power BI, I didn't catch the way you performed the crossjoin between the tables, if you have done it using the "Merge query" tool or via DAX formula, or doing steps in both.

May you please clarify this?

 

Thanks

Alberto