Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Mister_T
Advocate I
Advocate I

Return the last value (percentage) before and the first value after a certain threshold

Hi forum,

I did some research, but unfortunatelly could not find a fitting solution:

I have my products connected with the meassure "Cumulative_%_Revenue"
Threshold.png


Now, I want to create

  • one 'Card' visual to display the last value before a threshold (threshold here: 90 %) --> '89 %' in my example
  • one 'Card' visual to display the first value after a threshold (threshold here: 90 %) --> '95 %' in my example

 

Can anybody please help me in creating the corresponding measure?

Thank you so much!

Mister_T


Example data:

Product A25%
Product B49%
Product C63%
Product D73%
Product E82%
Product F89%
Product G95%
Product H97%
Product I99%
Product J99%
Product K100%

 

1 ACCEPTED SOLUTION
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Mister_T 

 

Is your Cumulative_%_Revenue DAX Measure?

 

Min Value =
VAR T1 =
    ADDCOLUMNS ( VALUES ( yourTable[Product] ), "Target", [Cumulative_%_Revenue] )
VAR Threshold = 0.9
RETURN
    MINX ( FILTER ( T1, [Target] >= Threshold ), [Target] )

 

View solution in original post

2 REPLIES 2
Vera_33
Resident Rockstar
Resident Rockstar

Hi @Mister_T 

 

Is your Cumulative_%_Revenue DAX Measure?

 

Min Value =
VAR T1 =
    ADDCOLUMNS ( VALUES ( yourTable[Product] ), "Target", [Cumulative_%_Revenue] )
VAR Threshold = 0.9
RETURN
    MINX ( FILTER ( T1, [Target] >= Threshold ), [Target] )

 

Thank you very much @Vera_33! Your solution works perfect for getting the first value higher then the threshold!

Yes, "Cumulative_%_Revenue" is a DAX meassure.

Just for completing the solution for other readers with the DAX meassure for getting the last value smaller then the threshold:

Max Value =
VAR T1 =
    ADDCOLUMNS ( VALUES ( yourTable[Product] ), "Target", [Cumulative_%_Revenue] )
VAR Threshold = 0.9
RETURN
    MAXX ( FILTER ( T1, [Target] <= Threshold ), [Target] )

 
Best regards,

Mister_T

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.