Filtere the date based on the values

[chotu27]

Hi All,

I need to hide the data to be filtered with dates for two categories. See below picture

I wanted to filter the table by Sale date before 01/23/2020 for only India and USA. other cuntries data should be show as it is with no filters. 

Country	Sales	Sale date
India	22226	01/2/2020
USA	62626	01/2/2020
Russia	25678	01/5/2020
UK	34576	01/6/2020
India	65423	01/30/2020
USA	87540	01/30/2020
Russia	34279	01/31/2020
UK	25280	01/31/2020

[v-lid-msft · Accepted solution]

Hi @chotu27 ,

We can try to use the following M code to requirement:

Table.SelectRows(#"Changed Type", each (([Country] = "India" or [Country]="USA") and [Sale date] < #date(2020,1,23)) or ([Country] <> "India" and [Country]<>"USA"))

All the queries are here:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45W8sxLyUxU0lEyAgIzIG1gqG+kb2RgZKAUqxOtFBrsCBQzMzJDlVMASwaVFhdD9JqamVtA5E2R5EO9gWLGJqbmUL1mSHIwa81MTYyMIdLGBsh6wRZbmJuaGGCRhdtsbGJkbglVYIhutZGpkYUBumQsAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Country = _t, Sales = _t, #"Sale date" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Country", type text}, {"Sales", Int64.Type}, {"Sale date", type date}}),
    #"Filtered Rows" = Table.SelectRows(#"Changed Type", each (([Country] = "India" or [Country]="USA") and [Sale date] < #date(2020,1,23)) or ([Country] <> "India" and [Country]<>"USA"))
in
    #"Filtered Rows"

Best regards,

[chotu27]

@v-lid-msft  I am getting error the feild of the record was not found.

[v-lid-msft]

Hi @chotu27 ,

How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?

Best regards,

[v-lid-msft]

Hi @chotu27 ,

Please try to change the #"Changed Type" to the name of the last step in your query, then change the [Country] and [Sale Date] to the name of your columns

Table.SelectRows(#"Changed Type", each (([Country] = "India" or [Country]="USA") and [Sale date] < #date(2020,1,23)) or ([Country] <> "India" and [Country]<>"USA"))

Best regards,

[tarunsingla]

Hi @chotu27 ,

Try adding a calculated column using the following DAX:

Filter = IF('Table'[Sale date] < DATE(2020, 1, 23), 1, IF(('Table'[Country] <> "India" && 'Table'[Country] <> "USA"), 1, 0))

And then use this calculated column in a filter to get the desired results.

[amitchandak]

In edit Query mode create a custom column

= table[country]= "India" and table[Sale date] < Date.FromText(23-feb-2020)

This will true and false , you can remove rows based on values.

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[VasTg]

@chotu27 

Do you want to do that in a visual or filter the actual data itself in the table?

[chotu27]

@VasTg  i want to filter the data itself in the table

[VasTg]

@chotu27 

In Power Query Editor, click on the dropdrop on Country column and choose filter->does not equal to. Choose Advanced and do the following.

Repeat the same step now for USA.

If it helps, mark it as a solution

Kudos are nice too

[chotu27]

by this approach  it is completely filtering out all data for india and usa , but i need to display data only before 01/23/2020 for india and usa 

[VasTg]

@chotu27 

What is your expected output?

By filter you mean, filter out(exclude) or filter in(include).

Change the condition after to before based on your need.