Forum Discussion

qmestu's avatar
qmestu
Helper IV
5 months ago
Solved

Filtering line chart with date

Hi,

 

I have a fact table and a calendar table, connected with a date field. I also have a slicer for the Quarter. How can i limit the value from the fact table in a line chart, so that it shows results for all the dates before the selection?

 

Thanks.

Need Help
  • MFelix's avatar
    MFelix
    5 months ago

    Hi qmestu ,

     

    Since the the slicer is a combination of year and quarter then you can use the disconnected table and with sync slicers that will allow you to get the same result and keep the interactions between all the charts.

     

    Can you please share a sample file so I can try and setup an example for you.

     

    Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

    If the information is sensitive please share it trough private message.

     

     

4 Replies

  • MFelix's avatar
    MFelix
    Super User
    5 months ago

    Hi qmestu ,

     

    One option is to have a slicer with the before option selected this will allow you to select a specific date and then get the value before that date.

     

    If you want to use a single select slicer then you need to have a disconnected table for the slicer so a copy or a partial copy of the calendar table since if you use the same table in the visual and in the slicer the values will be croped to the ones selected on the slicer. Then add a measure similar to this one:

     

    Values before selection =
VAR _MaximumDate = MAX(DisconnectedTable[Date])
VAR _Values = CALCULATE([Measure], Calendar[Date] <= _MaximumDate)
Return
_Values

     

    This should return expected result.

     

    • qmestu's avatar
      qmestu
      Helper IV
      5 months ago

      Hi,

       

      A slicer with the before option is not possible, since my slicer is a combination of year and quarter.

      The slicer also filters other visuals on the same page, so it can't be a disconnected table.

       

      Thanks.

      • MFelix's avatar
        MFelix
        Super User
        5 months ago

        Hi qmestu ,

         

        Since the the slicer is a combination of year and quarter then you can use the disconnected table and with sync slicers that will allow you to get the same result and keep the interactions between all the charts.

         

        Can you please share a sample file so I can try and setup an example for you.

         

        Can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

        If the information is sensitive please share it trough private message.