Forum Discussion
Filtere the date based on the values
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 |
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,
11 Replies
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 LinkedinHi 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,v-lid-msft I am getting error the feild of the record was not found.
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.
