Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.
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 |
Solved! Go to 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,
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,
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,
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,
Hi @chotu27 ,
Try adding a calculated column using the following DAX:
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
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
Check out the November 2023 Power BI update to learn about new features.
Read the latest Fabric Community announcements, including updates on Power BI, Synapse, Data Factory and Data Activator.