power query | search all

[hood2media]

hi,

i have managed to create a query parameter list called "MainAirport" that will filter 2 columns (i.e. ORIGIN & DEST) using following formula:

= Table.SelectRows( #"Promoted Headers", each [ORIGIN]=MainAirport or [DEST]=MainAirport)

however, i'm also trying to find all using following formula but without success:

= Table.SelectRows( #"Promoted Headers", each List.Contains({[ORIGIN], [DEST]}, MainAirport) or MainAirport = "All")

i am testing following DAX formula to calculate the total for diversion for DEST that doesn't produce the correct result -

Dv-Ar =

VAR Airport =

    SELECTEDVALUE( MainAirport[MainAirport] )

RETURN

CALCULATE(

    SUM( Master_Data[DIVERTED] ),

        Master_Data[DEST] = Airport

any help is much appreciated.

tks & kgrdds

[Mahesh0016 · Accepted solution]

@hood2media 

Dv-Ar =

VAR Airport =

    SELECTEDVALUE( MainAirport[MainAirport] )

RETURN

CALCULATE(

    SUM( Master_Data[DIVERTED] ),

       FILTER(Master_Data , Master_Data[DEST] = Airport)

@hood2media I hope it's helps you!!

[hood2media]

hi & tks, @Mahesh0016.

it seems to work!

kindly advise the dax formula to chk for all too i.e. regardless of the ORIGIN and DEST.
krgds, -nik

[hood2media]

hi @Mahesh0016,

i think i hv found the answer for all airports (regardless of the ORIGIN and DEST)-

VAR SelectedAirport =

    SELECTEDVALUE( StnList[StnList] )

RETURN

IF(

    ISBLANK(SelectedAirport),

    CALCULATE(

        SUM( Master_Data[DIVERTED] ),

        ALL( Master_Data )

    ),

    CALCULATE(

        SUM( Master_Data[DIVERTED] ),

        FILTER( Master_Data,

            Master_Data[DEST] = SelectedAirport

        )

    )

)

krgds, -nik