Filter Datetime Column based on another Column

[johnwrite]

Hi Guys ,

I am new to power BI .I want the visuals to interact in such a way that only some of the filters in the selected visual apply to the other visual, while other filters do not. Below is the data table i have 

 Now what I need is the rows of 'day' and 'count' present in between 'cws' and 'cwe' . Null values will be there for first two columns , so i am unable to filter it by comparing day with cws and cwe columns.  By having a visual filter for cws and cwe , it should be able to show me the day and counts between september 3rd and september 11th i.e., September 6th and september 7th from day column along with their counts . 

I am also fine if I can have another line that starts from september 6th and end with september 7th .  Any inputs/solution on this would be really helpful to me .

 Thanks

[v-shex-msft · Accepted solution]

Hi @johnwrite,

According to your description, you want to use the column “cws” and “cwe” to filter the records, right?

If as I said, you can refer to below steps:

Source table:

 

Open the Advanced Editor to add two variables to get the values from “cws“ and “cwe”.

Formulas:

Cws = List.First(Table.SelectRows(Table.SelectColumns(#"Changed Type","Cws"), each [Cws] <> null)[Cws]),
Cwe =List.First(Table.SelectRows(Table.SelectColumns(#"Changed Type","Cwe"), each [Cwe] <> null)[Cwe]),

Add a filter rows function and use above variables to filter records.

Formula:
#"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Day] >= Cws and [Day] <= Cwe )

Result:

 

Full query:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WUtIBIQt9YwN9IwNDMyDbzFIpVgdJwhAugSRuqQ8XNjZGETeCiRsZooibwcWNUMTN0cyx1DeGiQAtgdsCQrGxAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Cws = _t, Cwe = _t, Day = _t, Count = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Cws", type date}, {"Cwe", type date}, {"Day", type date}, {"Count", Int64.Type}}),
    Cws = List.First(Table.SelectRows(Table.SelectColumns(#"Changed Type","Cws"), each [Cws] <> null)[Cws]),
    Cwe =List.First(Table.SelectRows(Table.SelectColumns(#"Changed Type","Cwe"), each [Cwe] <> null)[Cwe]),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each [Day] >= Cws and [Day] <= Cwe )
in
#"Filtered Rows"

Reference:

List.First
Table.SelecRows

Table.SelectColumns

Regards,
Xiaoxin Sheng