Forum Discussion

Binway's avatar
Binway
Helper II
5 years ago
Solved

Line chart show 0 for missing data and when slicer applied

Hi Folks, I have a data and date table where I am trying to have the line chart drop to 0 when there is missing data. month_starting Business Sales 01/01/2015 FloatyMcBoat 24 01/02/201...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    5 years ago

    Hi Binway ,

     

    Please change the relationship between your date table and fact table to single direction

     

     

    Then use the following measure:

     

    New Amounts = 
VAR __min = CALCULATE ( MIN ( MonthlySales[month_starting]), ALLSELECTED(MonthlySales))
VAR __max = CALCULATE ( MAX ( MonthlySales[month_starting]), ALLSELECTED(MonthlySales))
VAR RESULT = IF(MAX(date_dim[first_day_of_month])>=DATE(YEAR(__min-10),MONTH(__min-10),1)&&MAX(date_dim[first_day_of_month])<=__max ,
                        SUM(MonthlySales[Sales])+0) return RESULT

     

     

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

     

    Best Regards,

    Dedmon Dai

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
5 years ago

Hi Binway ,

 

It's better that you can share us some sample pbix file.

 

Best Regards,

Dedmon Dai

Binway's avatar
Binway
Helper II
5 years ago

Thanks for looking at this.

You should be able to get the pbix from google drive here - LineChartTest 

All I am trying to do is add 0 for the missing values when connected to the date_dim, which works fine but no matter what I do I can't then filter this data down to just display the date values in the data table.

Binway

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Binway ,

     

    Please check the permission of the shared file. I can't download it.

     

    Best Regards,

    Dedmon Dai

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Community Support
    5 years ago

    Hi Binway ,

     

    Please change the relationship between your date table and fact table to single direction

     

     

    Then use the following measure:

     

    New Amounts = 
VAR __min = CALCULATE ( MIN ( MonthlySales[month_starting]), ALLSELECTED(MonthlySales))
VAR __max = CALCULATE ( MAX ( MonthlySales[month_starting]), ALLSELECTED(MonthlySales))
VAR RESULT = IF(MAX(date_dim[first_day_of_month])>=DATE(YEAR(__min-10),MONTH(__min-10),1)&&MAX(date_dim[first_day_of_month])<=__max ,
                        SUM(MonthlySales[Sales])+0) return RESULT

     

     

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

     

    Best Regards,

    Dedmon Dai

  • Binway's avatar
    Binway
    Helper II
    5 years ago

    Thanks so much Demon,

    I'll see how it works on the real data.

     

    Thanks again.

    Binway