Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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
Solved! Go to Solution.
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
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. |
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
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
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. |
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
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
Thanks , that measure worked perfectly , will try and learn from this , thanks so much for taking out time to look into this for me
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
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
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 ,
An easier fix would be , if the count of max Location was less than 7 , then leave Location as blank , as not enough data.
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
85 | |
67 |