topic Re: How Can I use DAX to achieve my desired goal below ( Pulling a list of values first and then min.) in DAX Commands and Tips

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

Anonymous — Thu, 24 Aug 2023 07:23:16 GMT

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

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

johnt75 — Thu, 24 Aug 2023 09:16:22 GMT

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 )

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

Anonymous — Thu, 24 Aug 2023 10:34:13 GMT

Unfortunately does not generate any output at all

Do I need the ISINSCOPE function ?

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

johnt75 — Thu, 24 Aug 2023 10:32:36 GMT

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

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

Anonymous — Fri, 25 Aug 2023 04:36:07 GMT

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

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

johnt75 — Fri, 25 Aug 2023 08:44:08 GMT

What are you trying to return exactly ?

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

Anonymous — Fri, 25 Aug 2023 09:02:38 GMT

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-p/3398648#M128267

I am trying to return a table..