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
hegde86
Frequent Visitor

Nearest Value of Selected Measure and corresponding other column values

I have a requirement where I will select a number from slicer and from data-set I need to look out a specific column based on slicer selected. If selected value is exactly matching with lookup value, display that, else get nearest value from data-set column and display it. For example,

I have a data-set with column as below 

Data-Set.PNG

 

And have a slicer as below.

Selected_Value.PNG

I selected 45 from slicer and based on value at Qty column, I need two values from Qty, i.e 43 lower limit & 52 Upper Limit. Along with that I also need value from next column. I.e for 43 it is 65% and 52 it is 73%. If slicer value matches exactly with Qty then just return that value from both Upper & Lower limit.

 

I achived getting 43 and 52 using below query with the help of @parry2k . 

Nearest Value Lower = 
VAR __selectedSlicerValue = SELECTEDVALUE( SlicerTable[Column] )
RETURN
CALCULATE(
MAX( YourTable[Qty] ), YourTable[Qty] <= __selectedSlicerValue
)

Nearest Value Upper = 
VAR __selectedSlicerValue = SELECTEDVALUE( SlicerTable[Column] )
RETURN
CALCULATE(
MIN( YourTable[Qty] ), YourTable[Qty] >= __selectedSlicerValue
)

But now I wanted next column values also. Is that possible in Power BI? if so please help me to get that also.

 

Thanks in Advance..

Rajesh

1 ACCEPTED SOLUTION
AlB
Super User
Super User

Hi @hegde86 

Yes, it should be relatively easy once you have the values you already calculated:

Nearest Value Lower 2 = 
CALCULATE(
DISTINCT( YourTable[Value] ), YourTable[Qty] = Nearest Value Lower
)
Nearest Value Upper 2 = CALCULATE( DISTINCT( YourTable[Value] ), YourTable[Qty] = Nearest Value Upper )

 

View solution in original post

2 REPLIES 2
AlB
Super User
Super User

Hi @hegde86 

Yes, it should be relatively easy once you have the values you already calculated:

Nearest Value Lower 2 = 
CALCULATE(
DISTINCT( YourTable[Value] ), YourTable[Qty] = Nearest Value Lower
)
Nearest Value Upper 2 = CALCULATE( DISTINCT( YourTable[Value] ), YourTable[Qty] = Nearest Value Upper )

 

hegde86
Frequent Visitor

Hi @AlB ,

 

Thanks for the solution. It is working as expected.

 

Thanks,

Rajesh S Hegde

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.