topic Re: Extension of a previous DAX Query in DAX Commands and Tips

Extension of a previous DAX Query

Anonymous — Fri, 25 Aug 2023 08:14:00 GMT

Hi Guys,

This is an extension of the query listed here :

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-Can-I-use-DAX-to-achieve-my-desired-goal-below-Pulling-a/m-p/3396761#M128147

So now instead of the minimum value I want to be able to generate a table that has both level and SRF values less than the current height

Desired out put :

Height	SRF
500	0.25
450	0.1
200	0.3
50	0.2

Table:-1 Extract the height parameter here

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

Table:-2 ( Now keeping 500 from table I need to extract the SRF values in table 2 corresponding to all heights less than 500 as ( 0.25, 0.1,0.3,0.2)

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

Desired table out put : I would then need to plot a scatter of these

height	SRF
500	0.25
450	0.1
200	0.3
50	0.2

Re: Extension of a previous DAX Query

johnt75 — Fri, 25 Aug 2023 09:16:52 GMT

I think you can use

New table = SELECTCOLUMNS( GENERATE( SELECTCOLUMNS( 'Table1', "Tower ID", 'Table1'[Tower ID], "MaxHeight", 'Table1'[Height] ), VAR MaxHeight = [MaxHeight] RETURN CALCULATETABLE( SELECTCOLUMNS( 'Table2', "height", 'Table2'[height], "SRF", 'Table2'[SRF] ), 'Table2'[height] <= MaxHeight ) ), "Tower ID", [Tower ID], "Height", [height], "SRF", [SRF] )

Re: Extension of a previous DAX Query

Anonymous — Tue, 29 Aug 2023 05:01:57 GMT

Hi , johnt75 thanks for the response..

I am now trying to get SRF's between levels defined by 2 variables which in turn have been defined as columns in the table... as shown below beween Midtop and Midbot But when I run this i get the error saying meomery not available and throws an error..
How can I incorporate multiple conditions in calculate table

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] )

could i get some help as how I can fix this ?

johnt75

Re: Extension of a previous DAX Query

Anonymous — Thu, 31 Aug 2023 03:12:18 GMT

Guys ,

Could Somebody please help me with this ? I am trying to introduce mutliple filters on the calculatetable function

Re: Extension of a previous DAX Query

ERD — Thu, 31 Aug 2023 07:01:36 GMT

Anonymous ,

1. Does Table 1 contain only 1 row?

2. Do you need a measure to filter Table 2 or a new calculated table?

Re: Extension of a previous DAX Query

Anonymous — Thu, 31 Aug 2023 07:15:41 GMT

Extremely sorry ERD , I have created a new thread for my problem and marking this solved ..

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Extraction-of-Values-Between-2-levels-Multiple-Filters-in/m-p/3408114#M128789

But to answer your quesiton , yes in my new query every towerID will have a single toplevel and bottomlevel..
Please take a look at it..