Forum Discussion

LEPR's avatar
LEPR
Frequent Visitor
2 years ago
Solved

Filter dates on a graph doesn't work with measure

Hi everyone,

 

I am displaying some data in a line and column chart. I have a filter for this chart to display the last 12 months only. I am using a separate Date table linked to my main dataset.

At first, I wanted the orange line (DETAILSUM) to show 0 values as well, and avoid the gaps when there is no data.

I found on this forum that I should create a measure like 

Detailsumcalc = SUM(CIM_INVOICE[DETAILSUM])+0
to do it. And it works ! But now the filter doesn't apply to this measure, and I get all the previous month displayed as well. The purple line is the Detailsumcalc, and the measure is in the same set CIM_INVOICE.
 

 

I couldn't find any workaround so far, so any idea would be appreciated.

 

Thank you

Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    Hi LEPR,

    Power bi chart will auto hide the blank parts, if you added +0 after the expression, they will expand the calculation range to all table rows.

    I think you need to add if statement to check the current row contents /filter effect and add +0 to the specific expression instead of directly add on the whole table level .

    Regards,

    Xiaoxin Sheng

3 Replies

  • VijayP's avatar
    VijayP
    Icon for Community Champion rankCommunity Champion
    2 years ago

    LEPR 

    You must add the date field to the Filtern pane and select the date range. Measures never control a filter pane

    • LEPR's avatar
      LEPR
      Frequent Visitor
      2 years ago

      Hi Vijay,

      Thanks for coming back to me on this topic, I am afraid I didn't get your point.

      The date is already in the filter panel for this visual, and it works as long as I have only normal fields in my graph, but not with this new measure that calculates 0 when there is no data in the months.

      Thanks

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        2 years ago

        Hi LEPR,

        Power bi chart will auto hide the blank parts, if you added +0 after the expression, they will expand the calculation range to all table rows.

        I think you need to add if statement to check the current row contents /filter effect and add +0 to the specific expression instead of directly add on the whole table level .

        Regards,

        Xiaoxin Sheng