Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

Reply
chotu27
Post Patron
Post Patron

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. 

 

CountrySalesSale date
India2222601/2/2020
USA6262601/2/2020 
Russia2567801/5/2020 
UK3457601/6/2020 
India6542301/30/2020 
USA8754001/30/2020 
Russia3427901/31/2020 
UK2528001/31/2020 
   
   
   
1 ACCEPTED SOLUTION
v-lid-msft
Community Support
Community Support

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"))

 

6.jpg

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

11 REPLIES 11
v-lid-msft
Community Support
Community Support

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"))

 

6.jpg

 

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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

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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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,

 

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

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.
 
Untitled.png
amitchandak
Super User
Super User

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
Memorable Member
Memorable Member

@chotu27 

 

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

Connect on LinkedIn

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

VasTg
Memorable Member
Memorable Member

@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.

image.png

 

Repeat the same step now for USA.

image.png

 

If it helps, mark it as a solution

Kudos are nice too

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 

VasTg
Memorable Member
Memorable Member

@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.

Connect on LinkedIn

Helpful resources

Announcements
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.