Forum Discussion

draxicle1's avatar
draxicle1
Helper I
1 year ago

Simple 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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
1 year ago

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's avatar
    draxicle1
    Helper I
    1 year ago

    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's avatar
      Anonymous
      Not applicable
      1 year ago

      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.

  • draxicle1's avatar
    draxicle1
    Helper I
    1 year ago

    Am I able to upload a redacted pbix file for you to look at ?

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      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's avatar
        draxicle1
        Helper I
        1 year ago

        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.