Simple table visual - display only rows that equal a measure.

[draxicle1]

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!

[Anonymous]

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,
Zhu

If 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 ?

[Anonymous]

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

[draxicle1]

Here is a link to some desensitised data.
https://drive.google.com/file/d/18pA8gWMv3fSneltTYfAVoL3DeoXy_vtG/view?usp=sharing

Please ignore or delete the "FY filter" and use the date slicer. The method works when 2024 is selected, but you'll notice that when you select 2023, that there are no "1" values and therefore nothing displays.  When you take away the Maxdateequals1 filter from the visual, the "1"s reappear.

[draxicle1]

Hi there. I wondered if you have been able to look at the file I sent . I am still struggling with this problem.

[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.

[Anonymous]

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,
Zhu

If there is any post helps, then please consider Accept it as the solution  to help the other members find it more quickly.

[Ritaf1983]

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:

 How to upload PBI in Community

[draxicle1]

Please 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?

[draxicle1]

The Maxdate is coming from a measure: