Forum Discussion

Binway's avatar
Binway
Icon for Helper II rankHelper 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
Icon for Community Support rankCommunity Support
5 years ago

Hi Binway ,

 

Please try to use the following measure:

 

measure =
VAR __min =
    CALCULATE ( MIN ( MonthlySales[month_starting] ), ALLSELECTED () )
VAR __max =
    CALCULATE ( MAX ( MonthlySales[month_starting] ), ALLSELECTED () )
VAR sales =
    SUMMARIZE (
        MonthlySales,
        MonthlySales[Business],
        date_dim[first_day_of_month],
        "QTY", SUM ( MonthlySales[Sales] ) + 0
    )
RETURN
    SUMX (
        FILTER (
            sales,
            date_dim[first_day_of_month] >= __min
                && date_dim[first_day_of_month] <= __max
        ),
        [QTY]
    )

 

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
Icon for Helper II rankHelper II
5 years ago

Sorry Dedmon - same result where the +0 is just not working if you filter it.  How would I create a "virtual table" with all the missing values, then filter that.

Thanks - appreciate the efforts.

  • v-deddai1-msft's avatar
    v-deddai1-msft
    Icon for Community Support rankCommunity 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
      Icon for Helper II rankHelper 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
        Icon for Community Support rankCommunity Support
        5 years ago

        Hi Binway ,

         

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

         

        Best Regards,

        Dedmon Dai