Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
vito123
Helper II
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
5000.25
4500.1
2000.3
500.2

 

 

 

Table:-1 Extract the height parameter here

Tower IDBottom Dia  Top DiaHeight
123Cell 2Cell 1500
 

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
123Cell 26500.3
123Cell 25000.25
123Cell 24500.1
123Cell 22000.3
123Cell 2500.2
 
Desired table out put : I would then need to plot a scatter of these 
 
height SRF
5000.25
4500.1
2000.3
500.2
 
1 ACCEPTED SOLUTION
johnt75
Super User
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]
)

View solution in original post

5 REPLIES 5
ERD
Super User
Super User

@vito123 , 

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!

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

vito123
Helper II
Helper II

Guys , 

 

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

johnt75
Super User
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]
)

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

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

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors