Showing the opposite/reverse of sliced dates in Power BI

[Anonymous]

I 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! 

[Anonymous · Accepted solution]

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. use this measure as a filter in the visual 

[Anonymous]

It worked! Thank you. 

[Anonymous]

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