Measure to include a multiple LookUP value not working

[Pandadev]

Hi I am trying to create formula , I wanted to add a final check to check if the maxLocationT was in a Table called bases and that was also looking at airline , to make each row of data in the table called hubs unique I created a column called FP Operator Airport , which is merged values of FP Operator&Arrival Airport. I then added two VAR in the formula below and looked to merge them for VLOOKUP looking at joined_ , but it does not work. Just checking to see if this is possible please. my formula is below

Hub = VAR baseDate_ =

TODAY ()

VAR maxLocationT_ =

TOPN (

1,

ADDCOLUMNS (

DISTINCT ( 'TU Feed'[Arrival Airport] ),

"@LocCount",

CALCULATE (

COUNT ( 'TU Feed'[Arrival Airport] ),

'TU Feed'[runway_departure] >= baseDate_ - 28,

'TU Feed'[runway_departure] < baseDate_ + 1

)

),

[@LocCount], DESC

)

VAR maxop_ =

TOPN (

1,

ADDCOLUMNS (

DISTINCT ( 'TU Feed'[FP Operator] ),

"@LocCount",

CALCULATE (

COUNT ( 'TU Feed'[FP Operator] ),

'TU Feed'[runway_departure] >= baseDate_ - 28,

'TU Feed'[runway_departure] < baseDate_ + 1

)

),

[@LocCount], DESC

)

VAR numMaxLocations_ =

COUNTROWS ( maxLocationT_ )

RETURN

IF (

numMaxLocations_ = 1,

IF (

MAXX (

maxLocationT_,

[@LocCount]

) >= 7,

VAR joined_ = (maxop_ & maxLocationT_)

LOOKUPVALUE('Hubs'[Type],

'Hubs'[FP Operator Airport],joined_,[FP Operator Airport]))

)

[MFelix · Accepted solution]

Hi @Pandadev ,

Without data is difficult to tell you what is not working one thing I notice on your measure is that the last variable JOINED_ is created but you don't have a return after it so it will keep not returnig values.

Measure updated below:

Hub =
VAR baseDate_ =
    TODAY ()
VAR maxLocationT_ =
    TOPN (
        1,
        ADDCOLUMNS (
            DISTINCT ( 'TU Feed'[Arrival Airport] ),
            "@LocCount",
                CALCULATE (
                    COUNT ( 'TU Feed'[Arrival Airport] ),
                    'TU Feed'[runway_departure] >= baseDate_ - 28,
                    'TU Feed'[runway_departure] < baseDate_ + 1
                )
        ),
        [@LocCount], DESC
    )
VAR maxop_ =
    TOPN (
        1,
        ADDCOLUMNS (
            DISTINCT ( 'TU Feed'[FP Operator] ),
            "@LocCount",
                CALCULATE (
                    COUNT ( 'TU Feed'[FP Operator] ),
                    'TU Feed'[runway_departure] >= baseDate_ - 28,
                    'TU Feed'[runway_departure] < baseDate_ + 1
                )
        ),
        [@LocCount], DESC
    )
VAR numMaxLocations_ =
    COUNTROWS ( maxLocationT_ )
RETURN
    IF (
        numMaxLocations_ = 1,
        IF (
            MAXX ( maxLocationT_, [@LocCount] ) >= 7,
            VAR joined_ = ( maxop_ & maxLocationT_ )
            RETURN
                LOOKUPVALUE (
                    'Hubs'[Type],
                    'Hubs'[FP Operator Airport], joined_,
                    [FP Operator Airport]
                )
        )
    )

Check if it works if not can you please share a mockup data or sample of your PBIX file. You can use a onedrive, google drive, we transfer or similar link to upload your files.

If the information is sensitive please share it trough private message.