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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 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

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
2 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

  • blader1989's avatar
    blader1989
    Helper II
    2 years ago

    Thank you Anonymous  for your wonderful reply. It solved my problem.