topic Re: Extraction of Values Between 2 levels - Multiple Filters in Calculated Table in DAX Commands and Tips

Extraction of Values Between 2 levels - Multiple Filters in Calculated Table

Anonymous — Thu, 31 Aug 2023 07:13:56 GMT

Hi Guys,

My problem here is an extension of what is here ..

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

I am trying to extract SRF values from a table titled 'TowerResultExtremeYielding' , between 2 levels or heights as indicated by

'MaxHeight; and 'MidBot' , both of which are available in a table called 'Bottom_Middle' as shown below:

Now I have this code below which works well for one filter which is < but I have 2 filters now and i need to filter between 2 levels

MID_Section_SRF_BYLEVEL = 
SELECTCOLUMNS(
	GENERATE(
		SELECTCOLUMNS(
			'Bottom_Middle',
			"Tower ID",Bottom_Middle[TowerModelId],
			"Midtop", Bottom_Middle[MIdtoplevel],
            "Midbot",Bottom_Middle[Bottom_Section_Height]
		),
		VAR MaxHeight = [Midtop]
        VAR Midbot=[Midbot]
		RETURN
			CALCULATETABLE(
				SELECTCOLUMNS(
					'TowerResultExtremeYielding',
					"height", TowerResultExtremeYielding[Level],
					"SRF", TowerResultExtremeYielding[Extreme_SRF],
                    "Loadcase", TowerResultExtremeYielding[LoadCase]
				),
				TowerResultExtremeYielding[Level] <= MaxHeight && TowerResultExtremeYielding[Level] >=Midbot
			)
	),
	"Tower ID", [Tower ID],
	"Height", [height],
	"SRF", [SRF],
    "Loadcase",[LoadCase]
)

as shown below but when I run this script I get an error with memory. Could somebody help me here ?

Re: Extraction of Values Between 2 levels - Multiple Filters in Calculated Table

ERD — Thu, 31 Aug 2023 08:26:53 GMT

Anonymous , can you provide the demos of initial tables (not screenshots) and the result you want to achieve (a new calculated table, a measure to filter some table, etc)?

Re: Extraction of Values Between 2 levels - Multiple Filters in Calculated Table

Anonymous — Thu, 31 Aug 2023 09:22:38 GMT

Hi ERD , Hope this helps:

Step:1 Table called 'Bottom_Middle' containing the relevant columns as below

Tower Model ID	Midtop Level	Bottom Section Height
123	345	20
124	500	200
125	400	10

Step:-2 Now we have another table called 'TowerResult ExtremeYielding' which contains the SRF value by level for each towerMOdel ID as shown below:

Tower Model ID	Level	SRF
123	345	1.2
123	300	2
123	250	1.2
123	10	1.5
124	600	2.1
124	500	1.2
124	400	1.1
124	300	1.4
124	100	1.1
125	450	1.3
125	400	2
125	10	3

Step:-3 Desired output is a TABLE that needs to contain the desired values for each towermodelID between the midtoplevel and Bottom Section Height as shown below:

Tower Model ID	Level	SRF
123	345	1.2
123	300	2
123	250	1.2
124	500	1.2
124	400	1.1
124	300	1.4
125	450	1.3
125	400	2
125	10	3

I hope this clarifies what I need , ERD , Please let me know if otherwise..

Re: Extraction of Values Between 2 levels - Multiple Filters in Calculated Table

ERD — Thu, 31 Aug 2023 11:46:40 GMT

Anonymous , here is your table:

FILTER ( SUMMARIZE ( 'TowerResult ExtremeYielding', Bottom_Middle[Tower Model ID], 'TowerResult ExtremeYielding'[ Level], 'TowerResult ExtremeYielding'[SRF] ), VAR _id = CALCULATE ( MAX ( Bottom_Middle[Tower Model ID] ) ) VAR midlevel = CALCULATE ( MAX ( Bottom_Middle[Midtop Level] ), Bottom_Middle[Tower Model ID] = _id ) VAR btmlevel = CALCULATE ( MAX ( Bottom_Middle[Bottom Section Height] ) ) RETURN [ Level] >= btmlevel && [ Level] <= midlevel )