Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
samw5
Helper II
Helper II

Relative Date filter --- not in last x days

I have a bunch of log data for computer scans. Each computer has multiple entries with a field called LastScan. 


I am trying to create a filter (ultimately to show a visual) for computer that have their latest scan over a certain period of time.

samw5_0-1598901438317.png

 

So in the example above, I need to know that the "LastScan" for the machine highlighted is 8/25/2020 not the other values and yet retain the rest of the data (ReleaseName). 

I tried to use the GroupBy in the Query Editor, however it makes the the other columns go away. If I add multiple grouping I am not able to get the correct data and end up with an entry for each date which isn't what I am trying to get. 

 

1 ACCEPTED SOLUTION
lkalawski
Memorable Member
Memorable Member

Hi @samw5

Your way to take advantage of Group By was very good. Probably the next step you took was "expand table" and you showed all your records.

Before you do that, add an extra column (step: Added Custom) that will select only the maximum rows - you can use this code:

 

 

 

let
    Source = [your source],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CompID", Int64.Type}, {"ReleaseName", Int64.Type}, {"Column1", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CompID"}, {{"MaxDate", each List.Max([Column1]), type nullable datetime}, {"Details", each _, type table [CompID=nullable number, ReleaseName=nullable number, Column1=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TopDate", each Table.Max([Details],"Column1")),
    #"Expanded TopDate" = Table.ExpandRecordColumn(#"Added Custom", "TopDate", {"ReleaseName", "Column2", "Column3"}, {"ReleaseName", "Column2", "Column3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded TopDate",{"Details"})
in
    #"Removed Columns"

 

 

 

 

 
I also prepared for you the file with solution. Please find it by using this link: https://gofile.io/d/FRY381

 

EDIT: file with your data: https://gofile.io/d/QjqJC0



_______________
If I helped, please accept the solution and give kudos! 😀 

View solution in original post

7 REPLIES 7
v-eqin-msft
Community Support
Community Support

Hi @samw5 ,

According to my understand, you want to highlight the ComputerID when its LastScan is not in last X days (chose by parameter),right?

 

You could follow these steps or look at the pbix  here

1. Create a what-if parameter for last X days like this:

9.1.4.1.png

2. Use the following formulas:

theLastestDate =
CALCULATE ( MAX ( 'Table'[LastScan] ), ALL ( 'Table' ) )
lastest by ComputerID =
CALCULATE (
    MAX ( 'Table'[LastScan] ),
    ALLEXCEPT ( 'Table', 'Table'[ComputerID] )
)
LastScan not in last X days =
VAR _diff = [theLastestDate] - [lastest by ComputerID]
RETURN
IF ( _diff <= MAX ( 'Last X days'[Last X days] ), 0, 1 )

 

3.Right-click the ComputerID in format Pane and select Conditional formatting -->Background color -->apply rules

My visualization looks like this:

9.1.4.2.png9.1.4.3.png

Is the result what you want? If you have any questions, please upload some data samples and expected output.

Please do mask sensitive data before uploading.

 

Best Regards,

Eyelyn Qin

This is a great solution! Thank you 👍

lkalawski
Memorable Member
Memorable Member

Hi @samw5

Your way to take advantage of Group By was very good. Probably the next step you took was "expand table" and you showed all your records.

Before you do that, add an extra column (step: Added Custom) that will select only the maximum rows - you can use this code:

 

 

 

let
    Source = [your source],
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"CompID", Int64.Type}, {"ReleaseName", Int64.Type}, {"Column1", type datetime}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"CompID"}, {{"MaxDate", each List.Max([Column1]), type nullable datetime}, {"Details", each _, type table [CompID=nullable number, ReleaseName=nullable number, Column1=nullable datetime]}}),
    #"Added Custom" = Table.AddColumn(#"Grouped Rows", "TopDate", each Table.Max([Details],"Column1")),
    #"Expanded TopDate" = Table.ExpandRecordColumn(#"Added Custom", "TopDate", {"ReleaseName", "Column2", "Column3"}, {"ReleaseName", "Column2", "Column3"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded TopDate",{"Details"})
in
    #"Removed Columns"

 

 

 

 

 
I also prepared for you the file with solution. Please find it by using this link: https://gofile.io/d/FRY381

 

EDIT: file with your data: https://gofile.io/d/QjqJC0



_______________
If I helped, please accept the solution and give kudos! 😀 

That works!

Stachu
Community Champion
Community Champion

Can you add sample tables (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1 Column2
A 1
B 2.5

You are looking for a PowerQuery solution, correct?



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Ultimately I don't really care if the filter is applied in PowerQuery, I'm just trying to create a table in PowerBI Desktop that will show the machines that haven't had a "LastScan" in the last x amount of days. 
Couldn't seem to figure out how to do this with the Releative Filter on the Visual.

 

ComputerIDReleaseNameLastScan
g:675541357663508719092020-08-30T13:04:01.0560000+00:00
g:6755413576635087KB4566116 (18362.1049)2020-08-30T13:04:01.0560000+00:00
g:6755413576635087KB4565483 (18362.959)2020-08-30T13:04:01.0560000+00:00
g:675541357663508720042020-08-30T13:04:01.0560000+00:00
g:6755413576635087KB4565351 (18362.1016)2020-08-30T13:04:01.0560000+00:00
g:6755413576635087KB4559004 (18362.997)2020-08-30T13:04:01.0560000+00:00
g:675541357663508719032020-08-25T04:33:51.0780000+00:00
g:6755413576635087KB4560960 (18362.900)2020-07-16T04:15:07.5150000+00:00
g:6896145144193208KB4565483 (18362.959)2020-08-30T13:56:57.6200000+00:00
g:6896145144193208KB4566116 (18362.1049)2020-08-30T13:56:57.6200000+00:00
g:689614514419320820042020-08-30T13:56:57.6200000+00:00
g:689614514419320819092020-08-30T13:56:57.6200000+00:00
g:6896145144193208KB4565351 (18362.1016)2020-08-30T13:56:57.6200000+00:00
g:6896145144193208KB4559004 (18362.997)2020-08-30T13:56:57.6200000+00:00
g:6896145144193208KB4560960 (18362.900)2020-07-28T19:45:24.4880000+00:00
g:6966508001147294KB4559004 (18362.997)2020-08-30T13:06:01.0850000+00:00
g:6966508001147294KB4565483 (18362.959)2020-08-30T13:06:01.0850000+00:00
g:696650800114729419092020-08-30T13:06:01.0850000+00:00
Stachu
Community Champion
Community Champion

you could do a measure like this:

NrOfComputersScanned = 
VAR __nrOfDaysInScope = 1
VAR __computers = GROUPBY('Table','Table'[ComputerID],"LatestScan",MAXX(CURRENTGROUP(),[LastScan]))
VAR __validComputers = FILTER(__computers,[LatestScan]>=TODAY()-__nrOfDaysInScope)
RETURN
COUNTROWS(__validComputers

that will limit the computer ids to the relevant ones, you just need to add whatever additional logic you want to calculate

you can change the number of days in scope using the first variable



Did I answer your question? Mark my post as a solution!
Thank you for the kudos 🙂

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.