Forum Discussion

Pandadev's avatar
Pandadev
Post Prodigy
5 years ago
Solved

Measure to include a multiple LookUP value not working

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]))
)
Need Help
  • MFelix's avatar
    MFelix
    5 years ago

    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.

1 Reply

  • MFelix's avatar
    MFelix
    Super User
    5 years ago

    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.