cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Showing the opposite/reverse of sliced dates in Power BI

have a table which consists of a column called "school.1" and a column called "dato.1" (date). Each row shows a school and a date IF the school had a canteen that day. If the school did not have a canteen that day, a row does n.

When I slice on dates, I would like to show the schools which did not have a canteen on those dates (i.e. the opposite).

 

I have the following example, where there exists schools A, B, C, D, and E.

 

School.1 dato.1

A 01-01-2020

B 01-01-2020

D 01-01-2020

A 02-01-2020

D 02-01-2020

 

If I slice on the 1.st of January 2020, schools C and E should be shown (as A, B, and D are shown), and if I slice on the 2.nd of January 2020, schools B, C, and E should be shown (as A, and D are shown). If I slice between the two dates only C and E should be shown.

 

I have tried using both DAX and the power query editor/M and I cannot make it work.

 

I hope you can help me! 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Helper V
Helper V

Re: Showing the opposite/reverse of sliced dates in Power BI

  1. Create a Table "AcList" using transform data in blank query paste below codes      
  2.  let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AUiIwMjA6VYnWglJ0whF0whsEYjTFVIQrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t, dat = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"School", type text}, {"dat", type date}}) in #"Changed Type"
  3. create another table with name School:
  4. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"School", type text}}) in #"Changed Type"
  5. Write a measure:  Measure = SWITCH(TRUE(),SELECTEDVALUE(School[School]) in SELECTCOLUMNS(AcList,"sc",AcList[School]),0,1)
  6. Final.PNG

     

    use this measure as a filter in the visual 

View solution in original post

3 REPLIES 3
Highlighted
Helper V
Helper V

Re: Showing the opposite/reverse of sliced dates in Power BI

  1. Create a Table "AcList" using transform data in blank query paste below codes      
  2.  let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AUiIwMjA6VYnWglJ0whF0whsEYjTFVIQrEA", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t, dat = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"School", type text}, {"dat", type date}}) in #"Changed Type"
  3. create another table with name School:
  4. let Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclSK1YlWcgKTzmDSBUy6KsXGAgA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t]), #"Changed Type" = Table.TransformColumnTypes(Source,{{"School", type text}}) in #"Changed Type"
  5. Write a measure:  Measure = SWITCH(TRUE(),SELECTEDVALUE(School[School]) in SELECTCOLUMNS(AcList,"sc",AcList[School]),0,1)
  6. Final.PNG

     

    use this measure as a filter in the visual 

View solution in original post

Highlighted
Helper I
Helper I

Re: Showing the opposite/reverse of sliced dates in Power BI

It worked! Thank you. 

Highlighted
Helper V
Helper V

Re: Showing the opposite/reverse of sliced dates in Power BI

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply

Helpful resources

Announcements
Community Conference

Power Platform Community Conference

Check out the on demand sessions that are available now!

Upcoming Events

Experience what’s next for Power BI

See the latest Power BI innovations, updates, and demos from the Microsoft Business Applications Launch Event.

secondImage

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Kudoed Authors