Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi I am trying to filter out all rows of data where there are less than or equalt to 2 matched ID_Code which has a date greater than Event Code.
I have multiple rows of data with many matching ID , all with dates , and I only want to get a count of dates after the event date.
So every row which has ID Code 001 , and has another 4 rows of data with ID Code 001 , that has a date greter than the event date , then show that total . Each ID Code , will have the same event date.
I was looking for something like this
ID Event Date departure date
001 10/03/2020 01/02/2020
001 10/03/2020 11/03/2020
001 10/03/2020 12/03/2020
001 10/03/2020 16/03/2020
My attempted column formula is not working , I wanted the number 3 to be in every row of the Total Column for ID 001
Solved! Go to Solution.
Hi,
You can not add measure to page filter pane, so please try this column instead of measure:
Column =
CALCULATE (
COUNT ( 'Table'[ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = EARLIER ( 'Table'[ID] )
&& 'Table'[departure date] > 'Table'[Event]
)
)
In my test sample, the result shows:
And then you can add this column to page filter to show the ID's(which count of depature date>event date is equal or more than 2) latest date:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
The formula seems right. do you have any filter on page
Check like this
Total = CALCULATE(COUNT('TU Feed'[ID]),FILTER(all('TU Feed'),'TU Feed'[departure_date]>'TU Feed'[Event Start Date]))
This will remove filter
the page I have the data showing is showing the latest departure date for each ID , when I add the column with the formula it shows the number 1 , even though there are more than one occurrence of the departure date greater than the event date. what my end goal is , is to only show the ID's latest date , where there has been 2 or more instances of that ID having a departure date greater than the event date , as I dont want to see the ones that just have 1 instance. i was hoping to add this measure to the page filter , to filter them out.
Hi,
You can not add measure to page filter pane, so please try this column instead of measure:
Column =
CALCULATE (
COUNT ( 'Table'[ID] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[ID] = EARLIER ( 'Table'[ID] )
&& 'Table'[departure date] > 'Table'[Event]
)
)
In my test sample, the result shows:
And then you can add this column to page filter to show the ID's(which count of depature date>event date is equal or more than 2) latest date:
Here is my test pbix file:
Hope this helps.
Best Regards,
Giotto
Thanks Giotto , that worked perfectly
Is it possible to amend this so it looks at departure date that is greater than the last 7 days
Column = CALCULATE ( COUNT ( 'Table'[ID] ), FILTER ( ALLSELECTED ( 'Table' ), 'Table'[ID] = EARLIER ( 'Table'[ID] ) && 'Table'[departure date] > 'Table'[Event] ))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
113 | |
97 | |
85 | |
70 | |
61 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |