Forum Discussion

chotu27's avatar
chotu27
Icon for Post Patron rankPost Patron
6 years ago
Solved

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 cuntr...
General Comment
Interesting Links
Need Help
Show and Tell
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    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,

     

v-lid-msft's avatar
v-lid-msft
Icon for Community Support rankCommunity Support
6 years ago

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's avatar
    chotu27
    Icon for Post Patron rankPost Patron
    6 years ago

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

    • tarunsingla's avatar
      tarunsingla
      Icon for Solution Sage rankSolution Sage
      6 years ago

      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.
       
    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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,

       

    • v-lid-msft's avatar
      v-lid-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      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,