Forum Discussion

blader1989's avatar
blader1989
Helper II
3 years ago
Solved

Filtering data visual when slicer selected

Dear all,

 

I'm building a  visual that can show previous data - assuming we want the visual will show last 3 months, 6 months....when we click the time in slicer but I can't still figure how to do after browsing all relevant topic even though I created a independent time time.

 

So it woule be great if you can guide me how to do. 

 

My apporach:

- The table and independent table time

- DAX for L3M that I wrote: 

L3M =
CALCULATE (
    SUM ( raw[Total (-VAT)] ),
    FILTER (
        ALL ( 'raw' ),
        raw[Billing Date] = MAX (IndependentCalendartbl[Date] )
            && raw[Billing Date] <= MAX (Calendartbl[Date])
            && raw[Billing Date] >= EDATE ( MAX(Calendartbl[Date]), -3 )
    )
)

My problem is when I choose the time slicer (for example May 2021), the visual only shown May -March-Feb like image below. But, it must shown April, March and Feb

When I choose January 2022, it didn't show previous December 2021-Nov 2021

So we need your help to guide us how to fix the DAX to show the outcomes we want: show previous months when we select time in slicer. Thank you so much for your time when reading my post!!  

Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi blader1989,

    After I replace the max to values function and change the operator to 'in' , the visual seems works well with filter selection. You can try to use following measure formula if help:

     

    L3M2 = 
CALCULATE (
    SUM ( raw[Total (-VAT)] ),
    FILTER (
        ALL ( 'raw' ),
        raw[Billing Date]
            IN VALUES ( IndependentCalendartbl[Date] )
                && raw[Billing Date] <= MAX ( Calendartbl[Date] )
                && raw[Billing Date] >= EDATE ( MAX ( Calendartbl[Date] ), -3 )
    )
)

     

     

    Notice:

    The max function only extracts the single value from aggregate row contents. So it may get the blank result(power bi visuals will auto hide the category that has blank result) if your table records not existed correspond records with date equal to the end date of each category year month group.

    Regards,

    Xiaoxin Sheng

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi blader1989,

    I think this should be related to the filter effect from table relationships, these filter effect will filter your visualizations with high priority.
    For this scenario, I'd like to suggest you create a new unconnected data table as source slicer.
    Then you can extract the selection use in Dax expression as condition to control calculation ranges to indirectly filter records on the visual.
    Regards,

    Xiaoxin Sheng

    • blader1989's avatar
      blader1989
      Helper II
      3 years ago

      Hi Anonymous , thank you for your reply. I already created a new unconnected date table for my purpose in my PBIX file

      So could you please guide me step by step as your comment? Thank you.

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi blader1989,

        After I replace the max to values function and change the operator to 'in' , the visual seems works well with filter selection. You can try to use following measure formula if help:

         

        L3M2 = 
CALCULATE (
    SUM ( raw[Total (-VAT)] ),
    FILTER (
        ALL ( 'raw' ),
        raw[Billing Date]
            IN VALUES ( IndependentCalendartbl[Date] )
                && raw[Billing Date] <= MAX ( Calendartbl[Date] )
                && raw[Billing Date] >= EDATE ( MAX ( Calendartbl[Date] ), -3 )
    )
)

         

         

        Notice:

        The max function only extracts the single value from aggregate row contents. So it may get the blank result(power bi visuals will auto hide the category that has blank result) if your table records not existed correspond records with date equal to the end date of each category year month group.

        Regards,

        Xiaoxin Sheng