Show the max value from slicer in a table

DAX DAX Calculations
1

Hello,

I am struggling to show the latest value from the slicer in a table. Here is my formula:

Display Value = 
VAR _month =
    MAX ( 'Wage Inflation DATE'[YearMonth] )
RETURN
    CALCULATE (
        SELECTEDVALUE ( 'Wage Inflation DATE'[YearMonth] ),
        ( 'Wage Inflation DATE'[YearMonth] = _month )
    )

The problem is:

image
image868Ă—419 26.8 KB

But I want to show in the table as the latest value selected. so instead of 202207, I want to see 202209.

PBIX: https://drive.google.com/file/d/1U3ztyzqCtP1JuM8G3bRqhJNZBfPFz4Vo/view?usp=share_link

Thanks in advance,
EZ

2

Hello @rit372002,

Thank You for posting your query onto the Forum.

It’s observed that in your data model, you’ve created “Many-to-Many Relationships” as well as have also activated the “Bi-Directional Relationships” between the tables. Below is the screenshot of the data model which you’ve created provided for the reference -

Original Data Model
Original Data Model1170Ă—740 33.6 KB

In order to achieve the results in this scenario, I’ve de-activated the “Many-to-Many Relationship” between the tables - “HRIS_SECURITY_VIEW_BUSINESS_UNIT_FINAL (3)” and “HRIS_TD_COMP_WAGE_DETAIL_NEWOLD” and have turned-off the “Bi-Directional Relationship” between the tables - “HRIS_TD_COMP_WAGE_DETAIL_NEWOLD” and “Wage Inflation DATE”. Below is the screenshot of the revised data model provided for the reference -

Revised Data Model
Revised Data Model1188Ă—752 34.5 KB

Once the data model is correctly sorted. Below is the DAX measure alongwith the screenshot of the final results provided for the reference -

Display Value - Harsh = 
CALCULATE( MAX( 'Wage Inflation DATE'[YearMonth] ) , 
    ALLSELECTED( 'Wage Inflation DATE' ) )

Final Results
Final Results1720Ă—640 55.6 KB

I’m also attaching the working of the PBIX file for the reference purposes.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Important Note: In case, you want to learn more about the data modelling aspects. Please go through the course of “Data Transformations and Modelling” which is already available onto our Enterprise DNA education portal.

Thanks and Warm Regards,
Harsh

Enterprise - Just Dyann - Harsh.pbix (8.6 MB)

1 Like
3

You are a superstar!

I did resolve it with MAXX by trial and error but I think your solution is much better as I was doing bi-directional and many to many relationships. Thank you so much!

This was my attempt…

Display Value = 
VAR _month =
    MAXX ( ALLSELECTED('Wage Inflation DATE'),'Wage Inflation DATE'[YearMonth])
RETURN
   _month
4

Hello @rit372002,

You’re Welcome!!! :slightly_smiling_face:

I’m glad that I was able to assist you with your query and thank you so much for the kind words of appreciation.

Thanks and Warm Regards,
Harsh