cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Extension of a previous DAX Query

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-desi...

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

1 ACCEPTED SOLUTION
Super User

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]
)``````
5 REPLIES 5
Super User

1. Does Table 1 contain only 1 row?

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

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!

Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

Helper II

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-level...

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..

Helper II

Guys ,

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

Super User

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]
)``````
Helper II

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],
),
TowerResultExtremeYielding[Level] <= MaxHeight && TowerResultExtremeYielding[Level] >=Midbot
)
),
"Tower ID", [Tower ID],
"Height", [height],
"SRF", [SRF],
)``````

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

@johnt75

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors