How Can I use DAX to achieve my desired goal below ( Pulling a list of values first and then min.)

[Anonymous]

Hi Guys, 

Say I have 2 tables

Table:-1 ( From Here I need to extract the value of Height = 500m)

Tower ID	Bottom Dia	Top Dia	Height
123	Cell 2	Cell 1	500

Table:-2 ( Now keeping 500 from table I need the minimum of the SRF values in table 2 corresponding to all heights less than 500 as min ( 0.25, 0.1,0.3,0.2) = 0.1 so on and so forth , so eventually for one tower ID I will have a single min SRF value.

Tower ID	Mass	height	SRF
123	Cell 2	650	0.3
123	Cell 2	500	0.25
123	Cell 2	450	0.1
123	Cell 2	200	0.3
123	Cell 2	50	0.2

How can I achieve this using DAX or otherwise ?

Sorry I am really new and not sure how to even get started on this

[Anonymous]

can somebody please help me here to return a table instead of the min value ?

[johnt75]

What are you trying to return exactly ?

[Anonymous]

Hi , 

I have created a new thread here below:

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Extension-of-a-previous-DAX-Query/m-...

I am trying to return a table..

[johnt75]

Make a one-to-many relationship from table 1 to table 2 and then you can try

Min SRF =
IF (
    ISINSCOPE ( 'Table1'[Tower ID] ),
    VAR CurrentHeight =
        SELECTEDVALUE ( 'Table1'[Height] )
    VAR Result =
        CALCULATE (
            MIN ( 'Table2'[SRF] ),
            KEEPFILTERS ( 'Table2'[Height] <= CurrentHeight )
        )
    RETURN
        Result
)

[Anonymous]

Unfortunately does not generate any output at all

Do I need the ISINSCOPE function ?

[johnt75 · Accepted solution]

The original code was intended for use as a measure. If you want to do it in a calculated column the below should work I think

Min SRF =
VAR CurrentHeight = 'Table1'[Height]
VAR Result =
    CALCULATE (
        MIN ( 'Table2'[SRF] ),
        KEEPFILTERS ( 'Table2'[Height] <= CurrentHeight )
    )
RETURN
    Result