Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Dynamic Slicer to Filter Data

HI There,

I am trying to create  a slicer/filter and chart like in excel  in which. I am having a datecolumn with TimeStamp and i want a view like below :

Expected Result :

TimePeriod Slicer 
-It will contains Today,Yesterday,Last Week, Last 2 Week and Last 4 week on the basis of StartDate :

 


On chart , Data should filter on the basis of below condions:

1) If I select Today() in filter /slicer then it will show only Today() Hours on the graph .

2) If I select Yesterday in filter /slicer then it will show only Yesterday Hours on the graph 
3) If I select Today() in filter /slicer then it will show only Last Week -Day wise data on the graph
4) If I select Last 4 weeks in filter /slicer then it will show only Last 4 week -(Week 1, week 2 ,week 3 , week4) wise data on the graph.

Please if anyone can help me in acheving this , will be really helpfull.

 

Thanks,

Ash
Ashish_Mathur 
 amitchandak 
SamWiseOwl 

Need Help
Show and Tell
Tutorial Requests

6 Replies

  • TomasAndersson's avatar
    TomasAndersson
    Solution Sage
    1 year ago

    Hi!
    Sounds like you want to create a calculation group. You can read more about it here: Create calculation groups in Power BI - Power BI | Microsoft Learn

    But basically you find calculation groups in the model view and create a new

     

    And then you create a number of calculation items that you later can use to dynamically slice a measure in different ways, such as 

    Yesterday = 
CALCULATE(
    SELECTEDMEASURE(),
    Dates[Date] = TODAY()
)

Today = 
CALCULATE(
    SELECTEDMEASURE(),
    Dates[Date] = TODAY() - 1
)

YTD = 
CALCULATE(
    SELECTEDMEASURE(),
    DATESYTD(Dates[Date])
)

     and so on

     

    Then you can just add the calculation group as a slicer and let it dynamically slice any measure you have. Try it out and let me know if you need any more help. Good luck!

  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi TomasAndersson ,

    I will  try to create the same however what is "Selectedmeasure()" ?
    Also i want to put my Today, yesterday, This week, last week and last 4 week weeks on X axis as well.

    So for that what should i do ?

    So let supopse if i selcteded Today() on Dynamic Slicer 

    Vlaues should appear only in Hours on the chart X axis 
    If i select last week then Days should appear on the chart X axis .

     

    Is there anyway to create this as well?

     

    Reagrds,

    Ash

    • TomasAndersson's avatar
      TomasAndersson
      Solution Sage
      1 year ago

      SELECTEDMEASURE() is a placeholder for a measure, any measure, that the calculation item will apply to. It is explained in the article i linked. So if you have [Sum if Sales] on the Y-axis and [Date] on the x-axis and choose 

      Yesterday = 
CALCULATE(
    SELECTEDMEASURE(),
    Dates[Date] = TODAY() - 1
)

       Only yesterdays value for [Sum of Sales] will show in the visual, and so on.

       

      The x-axis can't be controlled directly this way, but if you have hierarchy of some sort on the x-axis (e.g. Year / Month / Day / Hour) the user will be able to drill up or down as needed.

       

      The alternative solution is to create several visuals and bookmarks and create buttons that hide/show the desired visual depending on your selection. But since you have so many time ranges you want to be able to slice by I think that approach will be too tedious. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        make sense tomas .

        Let me try creating this way .

         

        thanks,

        ash