Forum Discussion
Helper ISimple table visual - display only rows that equal a measure.
Hi there,
I have a simple table visual with some columns linked to an excel workbook.
I would like to show only rows where the column "Date of Evaluation" is a certain date.
The date is defined by a measure "Maxdate" which calculates the latest date in that period.
The measure itself works.
Question: Is there a way to filter and display only rows in that table visual where the column "Date of Evaluation" equals what the measure "Maxdate" calculates?
I've tried using the filters pane for that visual, but it won't accept a measure. It only accepts date values.
Thanks!
11 Replies
- Ritaf1983
Super User
Hi draxicle1
Please share sample data (excluding sensitive information) in text format, along with your expected result and any relevant logic. For guidance, refer to
How to provide sample data in the Power BI Forum
If possible, upload a simplified .pbix file using this guide:
draxicle1Please see a stripped down the data table (see below)
I have also attached a screenshot of the dashboard. The yellow outline is the table visual taken from the excel workbook. The Green outline is the measure "Maxdate" that shows the latest date in the period.
I would like only the rows where "Date of Evaluation" equals the "Maxdate" measure to display in the visual.
ID
Department Objective Description Date of Evaluation OB-24-001 1a) 31-Dec-24 OB-24-001 1a) 31-Mar-25 OB-24-002 1b) 31-Dec-24 OB-24-002 1b) 31-Mar-25 OB-24-003 2) 31-Dec-24 OB-24-003 2) 31-Mar-25 - Ritaf1983
And from where is the max date coming?
Thanks for the replies from Ritaf1983.
Hi draxicle1 ,
Create two measure:
Maxdate = CALCULATE(MAX('Objective Scorecard'[Date of Evaluation]),ALL('Objective Scorecard'))Measure = IF(MAX('Objective Scorecard'[Date of Evaluation])=[Maxdate],1,0)Then filter out data with measure equal to 1:
Result:
Best Regards,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- draxicle1
Hi there.
Many thanks for this. It makes perfect sense.
Unfortunately it does not seem to work in the pbix I have.
Green is the 1/0 value measure when Date of Evaluation = Maxdate
Orange is Maxdate2 (slightly modified to work with slicers, removing ALL)
Blue is the Filter.
As you can see it is pulling all rows in still.
Hi draxicle1 ,
Another way to do this is to create a calculated column:
IsMaxDate = IF('Objective Scorecard'[Date of Evaluation]=[Maxdate],1,0)Filter the calculated column with the value of 1:
Best Regards,
ZhuIf there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- draxicle1
Am I able to upload a redacted pbix file for you to look at ?
Hi draxicle1 ,
You can upload your file to a cloud storage (like OneDrive, Dropbox, Google Drive or Wetransfer for example) and paste the link to that storage location in the post. Please ensure sensitive data is removed and the correct access permission is set.
Best Regards,
Zhu
