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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

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
ERD
Community Champion
Community Champion

@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
Community Champion
Community Champion

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

ERD
Community Champion
Community Champion

@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
Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Jan NL Carousel

Fabric Community Update - January 2025

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