Forum Discussion
Using a date slicer then applying an additional date based filter on a chart
Hi
I'm still a relative newbie to Power BI so please excuse if this is simple - I just can't get my head around what I need to do.
I have a database based on offending. I have an offence table which has a person ID, offence type and offence date. I have also created columns for Year and Quarter (fiscal) based on the offence date.
A person can only have one offence per quarter but can appear in every quarter.
I need my chart to count the number of offences by offence type based on the slicer selection. If I select Quarter, this is fairly straightforward as the number of offences is the same as the number of people. However, if I select year, I only want the chart to count the earliest offence date for each person. I've tried creating a firstdate measure for the offence date but didn't know what to do with it once I had! Any advice would be great. Thank you!
A very simplified example of data below:
Change it to the following measure, measure only considers the current row context, you must set it to ALl(table).
Measure = CALCULATE(FIRSTDATE('Table'[Column1]),ALL('Table'))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
3 Replies
What does the measure you've written look like?
Also, it's good practice to create a Calendar table for data like this. You can create a relationship between DateOffence and date in the calendar table. Then create the Quarter, Year fields etc in the Calendar table (and not in the Fact table as shown)
It's just a basic
FirstOffenceDate = FIRSTDATE(Offences[Date Offence])I was hoping that I could use the slicer to filter the correct date range and then I could use this to filter the data further to the first offence date... but I get very confused with filters!Thanks for the calendar tip. I'll add that in to the model.Thank you
Change it to the following measure, measure only considers the current row context, you must set it to ALl(table).
Measure = CALCULATE(FIRSTDATE('Table'[Column1]),ALL('Table'))Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.
