Forum Discussion

ecroke's avatar
ecroke
Frequent Visitor
9 years ago
Solved

Filter Drop down

Hi, I am trying to recreate a column chart within Power Bi. The aspect I'm having trouble with is that I must put in a drop down box that lets you filter the chart into 5 different aspects. 1. Last...
  • parry2k's avatar
    parry2k
    9 years ago

    hey i tried to create a sample for you, see pbix file here

     

    https://drive.google.com/file/d/0B8z5lc1oML-hT3pfMVI1OE5QRFU/view?usp=sharing

     

    Basically adding some measures in calendar table and then filtered on those measures. once you have your data table related to calendar table, i expect it will filter record based on your slicer. anyhow this is an idea, you can change/amend as you see fit.

     

    Let me know how it goes. 

     

     

  • v-sihou-msft's avatar
    v-sihou-msft
    9 years ago

    ecroke

     

    To filter the "Last X period" data, you can create a calculated table to include all "Last X periods" dates and add a column for tagging "Last X period". Then build the relationship to existing date table to filter corresponding dates. For more details, please refer to article below:

     

    Power BI – Time Period Slicer for Last 7 Days,Last 30 Days..

     

    Regards,

Back2Basics's avatar
Back2Basics
Resolver I
9 years ago

Hi ecroke

 

I'm new to this too but if it were me I would try to use a 'dynamic' column, or several columns to identify when they fall into those catergories.

 

For example (Last 30 days):

 

Last 30 days = if(Table_A[Date Field] > (Today()- 30), "Last 30 Days", "")

*This, as far as I am aware, should return "Last 30 Days" where the date in questio nis greater than today -30, and therefore should capture anything that falls into that. As the data increases or the data changes records will fall in or out of this so will be effectively 'LIVE'.

 

You might be able to do it all in one expression:

 

if(Table_A[Date Field] >(Today () -30), "Last 30 Days", if(Table_A[Date Field] >(Today() -(30*7)), "Last 30 Weeks", if(Table_A[Date Field] >(Today() -365), "last 12 Months", if(Table_A[Date Field] >(Today()-730), "last 8 Quarters", ""))))

 

I might be a bit naive about the caluations in there and there might be better ways of perfecting those - but hopefully you can see the idea behind my thinking here??