Skip to main content
cancel
Showing results for 
Search instead 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

Reply
vito123
Helper II
Helper II

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

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

 

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:

vito123_0-1693465818035.pngvito123_1-1693465828836.png

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 ? 

1 ACCEPTED SOLUTION

@vito123 , 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
)

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!

View solution in original post

3 REPLIES 3
ERD
Super User
Super User

@vito123 , 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)?

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!

Hi @ERD , Hope this helps:

 

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

Tower Model IDMidtop LevelBottom Section Height
123 34520
124500200
12540010

 

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 LevelSRF
123 3451.2
1233002
1232501.2

123

101.5
1246002.1
1245001.2
1244001.1
1243001.4
1241001.1
1254501.3
1254002
125103

 

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 LevelSRF
123 3451.2
1233002
1232501.2
1245001.2
1244001.1
1243001.4
1254501.3
1254002
125103

 

 

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

@vito123 , 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
)

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!

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors