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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

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
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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

Top Solution Authors