Forum Discussion

hood2media's avatar
hood2media
Resolver II
3 years ago
Solved

power query | search all

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

 

 

 

 

Need Help
Show and Tell
  • Mahesh0016's avatar
    Mahesh0016
    3 years ago
    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!!

3 Replies

  • Mahesh0016's avatar
    Mahesh0016
    Super User
    3 years ago
    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's avatar
      hood2media
      Resolver II
      3 years ago

      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's avatar
        hood2media
        Resolver II
        3 years ago

        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