Measure to identify location that is matched the most based on ID in the last 14 days

[Pandadev]

Hi I am trying to get a measure to get the max location based on ID code and dates within the last 14 days , dynamic so it is always 14 days from todays date

Table Structure is

DateTime

ID

Location

This Table is called TU Table

ID      DateTime             Location

-233 01/12/2020 12:35 LONDON

-233 01/12/2020 16:35 MANCHESTER

-233 01/12/2020 20:35 LONDON

-233 02/12/2020 12:35 MANCHESTER

-233 02/12/2020 16:35 LONDON

-233 02/12/2020 20:35 MANCHESTER

-233 03/12/2020 12:35 LONDON

-233 03/12/2020 16:35 MANCHESTER

-233 03/12/2020 20:35 LONDON

-233 04/12/2020 12:35 MANCHESTER

-233 04/12/2020 16:35 LONDON

-233 04/12/2020 20:35 MANCHESTER

-232 01/12/2020 12:35 LONDON

-232 01/12/2020 16:35 MANCHESTER

-232 01/12/2020 20:35 LONDON

-232 02/12/2020 12:35 MANCHESTER

-232 02/12/2020 16:35 LONDON

-232 02/12/2020 20:35 MANCHESTER

-232 03/12/2020 12:35 LONDON

-232 03/12/2020 16:35 MANCHESTER

-232 03/12/2020 20:35 LONDON

-232 04/12/2020 12:35 MANCHESTER

-232 04/12/2020 16:35 LONDON

-232 04/12/2020 20:35 MANCHESTER

So for ID -233 it would be LONDON and ID -232 would be MANCHESTER . If the max is the same , so say there are 10 matches for LONDON and 10 matches for MANCHESTER , then either leave blank or add unknown

[AlB]

@Pandadev 

Try just using LOOKUPVALUE to get the country

MeasureV2B =
VAR baseDate_ =
    TODAY ()
VAR maxLocationT_ =
    TOPN (
        1,
        ADDCOLUMNS (
            DISTINCT ( Table1[Location] ),
            "@LocCount",
                CALCULATE (
                    COUNT ( Table1[Location] ),
                    Table1[DateTime] >= baseDate_ - 14,
                    Table1[DateTime] < baseDate_ + 1
                )
        ),
        [@LocCount], DESC
    )
VAR numMaxLocations_ =
    COUNTROWS ( maxLocationT_ )
RETURN
    IF (
        numMaxLocations_ = 1,
        IF (
            MAXX ( maxLocationT_, [@LocCount] ) >= 7,
            LOOKUPVALUE (
                Table1[BaseCountry],
                Table1[Location], MAXX ( maxLocationT_, [Location] )
            )
        )
    )

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[Pandadev]

Hi I am trying to learn from your 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]))

)

[Pandadev]

Thanks that worked great , my final task is to check if the Column 'TU Feed' [Op Country] = MesureV2B , is there a way to compare the column value with measured value

[AlB]

@Pandadev 

I don't understand the question. Can you provide an example? Where are you going to use this comparison?

Please accept the solution when done and consider giving a thumbs up if posts are helpful.  Contact me privately for support with any larger-scale BI needs, tutoring, etc.

[AlB · Accepted solution]

@Pandadev 

Measure V2=
VAR baseDate_ =
    TODAY ()
VAR maxLocationT_ =
    TOPN (
        1,
        ADDCOLUMNS (
            DISTINCT ( Table1[Location] ),
            "@LocCount",
                CALCULATE (
                    COUNT ( Table1[Location] ),
                    Table1[DateTime] >= baseDate_ - 14,
                    Table1[DateTime] < baseDate_ + 1
                )
        ),
        [@LocCount], DESC
    )
VAR numMaxLocations_ =
    COUNTROWS ( maxLocationT_ )
RETURN
    IF (
        numMaxLocations_ = 1,
        IF (
            MAXX ( maxLocationT_, [@LocCount] ) >= 7,
            MAXX ( maxLocationT_, [Location] )
        )
    )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Pandadev]

Is it possible to get the country of the max [Location] , I have and additional Column Called BaseCountry.  The formula works great to bring in the Base Name , but If could also have the Base Country , then that would be great. thanks

[Pandadev]

Thanks , that measure worked perfectly , will try and learn from this , thanks so much for taking out time to look into this for me

[AlB]

@Pandadev 

I don't understand. Please provide an example with the expected result to clarify.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[AlB]

Hi @Pandadev 

1. Place ID in  a table visual

2. Place this measure in the visual

Measure =
VAR baseDate_ = TODAY ()
VAR maxLocationT_ =
    TOPN (
        1,
        DISTINCT ( Table1[ Location] ),
        CALCULATE (
            COUNT ( Table1[ Location] ),
            Table1[DateTime] >= baseDate_ - 14,
            Table1[DateTime] < baseDate_ + 1
        ), DESC
    )
VAR numMaxLocations_ =
    COUNTROWS ( maxLocationT_ )
RETURN
    IF ( numMaxLocations_ = 1, maxLocationT_ )

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[Pandadev]

Thanks for the formula , is working , is it possible to include if latest date , and column called status equals storage , then return the max location as last date Location , 

[Pandadev]

An easier fix would be , if the count of max Location was less than 7 , then leave Location as blank , as not enough data.