Displaying opposite/reverse of sliced dates

[Anonymous]

Hi.

I have a table where a row shows a school, a start date of having a canteen at the school, a finish date of having a canteen at the school, and the type of canteen.  When I slice on specific dates I would like to show, which schools do not have a canteen on the specific dates (i.e. the opposite).

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

School        |        Start          |     Finish         |      Type of canteen
A                |  01.01.2020       | 15.01.2020     |               1
A                |  01.01.2019       | 15.01.2019     |               1
A                |  01.01.2020       | 30.03.2020     |               2
B                |  01.01.2019       | 15.01.2019     |               1
C                |      null              | null                 |             null
D                |  01.01.2020      |15.01.2020       |              2
D                |  01.12.2019      |  01.02.2020     |              1
E                 |      null             |  null                |             null

If I slice on the 2nd of January 2020, schools B, C and E should be shown. If I slice on the third of March, schools B, C, D, and E should be shown. And of course, If I slice on the 1st of January 2018 all schools should be shown.  

I hope you can help me!

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

Calendar(a calculated table):

Calendar = CALENDAR(DATE(2018,1,1),DATE(2020,12,31))

There is no relationship between two tables. You may create a measure as below.

Visual Control = 
var _mindate = 
CALCULATE(
    MIN('Calendar'[Date]),
    ALLSELECTED('Calendar')
)
var _maxdate = 
CALCULATE(
    MAX('Calendar'[Date]),
    ALLSELECTED('Calendar')
)
var _start = SELECTEDVALUE('Table'[Start])
var _end = SELECTEDVALUE('Table'[End])
return
IF(
    OR(
        OR(
                _end<_mindate,
                _start>_maxdate
        ),
        ISBLANK(SELECTEDVALUE('Table'[Type of canteen]))
    ),
    1,0
)

Then you need to put the measure in the visual level filter to get the result.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi Allan.

I looked at the pbix file. When I tried to slice on the 2.nd of January School A appeared, which it should not.

Kind Regards

Louise.

[Anonymous · Accepted solution]

1. Create the source table name:"t1"

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIwVPBKzCtNLKpUMDIwMgCKGJpiiCjF6mBRbWiJrhoigl012CRjAwXfxKLkDBjfCKzWiSSTnYEsKAJxXYj0hBGyapfU5NTcpNQimMlAIbfUpCJMT7siWRYLAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t, Start = _t, Finish = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"School", type text}, {"Start", type date}, {"Finish", type date}, {"Type", Int64.Type}})
in
    #"Changed Type"

2. A date table(Calendar):Calculated table name it Date.(Use Modelling tab to add New Table Paste below Dax

Date = CALENDAR(DATE(2018,1,1),MAX(t1[Finish]))

3. create a table "School" using below dax:

School = DISTINCT(SELECTCOLUMNS(t1,"School",t1[School]))

4.Create a measure 

Measure = 
var t2=SELECTCOLUMNS(t1,"sc",t1[School],"Flag",IF(ISBLANK(t1[Finish]),FALSE(),t1[Finish]>= SELECTEDVALUE('Date'[Date])&&t1[Start]<= SELECTEDVALUE('Date'[Date])))
return
SWITCH(TRUE(),NOT(SELECTEDVALUE(School[School])) in SELECTCOLUMNS(filter(t2,[Flag]=TRUE()),"sc",[sc]),1,0)

5. Apply this measure in visual filter as below screenshot

s.3.3.20202.1.2020New table and Filter pane selected date 1.1..2018

[Anonymous]

Hi @Anonymous 

This worked perfectly! Thank you.

[Anonymous]

For date range use this measure:

Measure = 
var t2=SELECTCOLUMNS(t1,"sc",t1[School],"Flag",IF(ISBLANK(t1[Finish]),FALSE(),(MIN('Date'[Date])<=t1[Finish] && t1[Finish] >= MAX('Date'[Date]))||(MIN('Date'[Date])<=t1[Start] && t1[Start]>= MAX('Date'[Date]))))
var t3=SELECTCOLUMNS(t1,"sc",t1[School],"Flag",IF(ISBLANK(t1[Finish]),FALSE(),t1[Type]= SELECTEDVALUE(t1[Type])))

return
SWITCH(TRUE(),NOT(SELECTEDVALUE(School[School])) in SELECTCOLUMNS(filter(t2,[Flag]=TRUE()),"sc",[sc])  &&  NOT(SELECTEDVALUE(School[School])) in SELECTCOLUMNS(filter(t3,[Flag]=TRUE()),"sc",[sc]),1,0)

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

[Anonymous]

@Anonymous : To include the canteen selection in the report

Change the Measure with below code Now:

Measure = 
var t2=SELECTCOLUMNS(t1,"sc",t1[School],"Flag",IF(ISBLANK(t1[Finish]),FALSE(),t1[Finish]>= SELECTEDVALUE('Date'[Date])&&t1[Start]<= SELECTEDVALUE('Date'[Date])))

var t3=SELECTCOLUMNS(t1,"sc",t1[School],"Flag",IF(ISBLANK(t1[Finish]),FALSE(),t1[Type]= SELECTEDVALUE(t1[Type])))

return
SWITCH(TRUE(),NOT(SELECTEDVALUE(School[School])) in SELECTCOLUMNS(filter(t2,[Flag]=TRUE()),"sc",[sc])  &&  NOT(SELECTEDVALUE(School[School])) in SELECTCOLUMNS(filter(t3,[Flag]=TRUE()),"sc",[sc]),1,0)

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

[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

[Anonymous]

try using this function:

let 
    notCanteen=(d)=> Text.Combine( List.Distinct(Table.SelectRows(school, each d<[Start] or d>[Finish])[School]), ",") 

in
    notCanteen

[Anonymous]

Hi @Anonymous 

You selected the 2nd of January and are shown schools A, B, and D, but I wanted schools B, C, and E to be shown? 
The slicing of the dates should also be in the report and not in the PowerQuery editor.

Kind Regards
Louise

[Anonymous]

Hi @Anonymous 

I corrected the script to match you data

have a look at the last post

[Jimmy801]

Hello @Anonymous 

you have to create a row for every day the canteen is open. This you can achive creating a list of dates of every row (so list contains the days when the canteen is open) and expand them to new rows. Here an example how this looks like

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AMiIwMg09AUwVSK1UGWN7SEy0OYaPJgTcYGegbGEKYRWN6JgH5nIAuKYFzszoEY5wKRNzSCmAFSaoTsXFck42IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t, Start = _t, Finish = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"School", type text}, {"Start", type date, "de-DE"}, {"Finish", type date, "de-DE"}, {"Type", Int64.Type}}),
    AddListOfDates = Table.AddColumn
    (
        #"Changed Type",
        "Dates",
        (add)=> if add[Start]<> null and add[Finish]<> null then List.Dates(add[Start], Duration.Days(add[Finish]-add[Start])+1, #duration(1,0,0,0)) else null
    ),
    ExpandToRows = Table.ExpandListColumn(AddListOfDates, "Dates")
in
    ExpandToRows

Copy paste this code to the advanced editor in a new blank query to see how the solution works.
If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi @Jimmy801 

This helps me showing the schools which have an open canteen the specific dates, yes.

However, what I am looking for is the schools which do NOT have a canteen open those specific dates.

Kind Regards Louise

[Jimmy801]

Hi @Anonymous 

I didn't see that 🙂

Here now an example for the closed dates. You have however to specify somewhere the date range. I did that in the step ListOfDatesForEvaluation 

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTIw1AMiIwMg09AUwVSK1UGWN7SEy0OYaPJgTcYGegbGEKYRWN6JgH5nIAuKYFzszoEY5wKRNzSCmAFSaoTsXFck42IB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [School = _t, Start = _t, Finish = _t, Type = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"School", type text}, {"Start", type date, "de-DE"}, {"Finish", type date, "de-DE"}, {"Type", Int64.Type}}),
    ListOfDatesForEvaluation = List.Dates(#date(2018,1,1), 1200,#duration(1,0,0,0)),
    AddListOfDates = Table.AddColumn
    (
        #"Changed Type",
        "Dates closed",
        (add)=> if add[Start]<> null and add[Finish]<> null then List.Difference(ListOfDatesForEvaluation, List.Dates(add[Start], Duration.Days(add[Finish]-add[Start])+1, #duration(1,0,0,0))) else ListOfDatesForEvaluation
    ),
    ExpandToRows = Table.ExpandListColumn(AddListOfDates, "Dates closed")
in
    ExpandToRows

Copy paste this code to the advanced editor in a new blank query to see how the solution works.

If this post helps or solves your problem, please mark it as solution (to help other users find useful content and to acknowledge the work of users that helped you)
Kudoes are nice too

Have fun

Jimmy

[Anonymous]

Hi @Jimmy801 

It almost worked. The thing is when I slice on the 2nd of January school A, shows up (due to row A,  01.01.2019, 15.01.2019, 1).

Kind Regards Louise.

[Anonymous]

ok.

try this now

let 
    notCanteen=(d)=>
    let 
    schools=List.Distinct(school[School]),
    snic=Text.Combine( List.Difference(schools,List.Distinct(Table.SelectRows(school, each d>[Start] and d<[Finish])[School])), ",") 
    in snic
in
    notCanteen

school is the name of the query containing your table

[Anonymous]

Hi @Anonymous 

This worked!!
However, it is the end-user of the reports who are supposed to slice the dates (which means the slicing cannot happen in the Power Query editor)...

[Jimmy801]

Hi @Anonymous 

therefore you need the table as I was stating. A table that has for every combination School/Type Canteen/and days off a row. Then the end-user is able to filter this in a visual

Hope that helps

Jimmy

[Anonymous]

<<..

This worked!!
However, it is the end-user of the reports who are supposed to slice the dates (which means the slicing cannot happen in the Power Query editor)...

..>>

Hi @Anonymous 

having posted the problem in the power Query section, I thought we needed a solution in the power query context.
I don't understand which interface you are referring to.
If you explain the details of what you expect, someone (probably not me) can help you.

[Anonymous]

it is just a workaround of what I understood from your "specs"

let
    notCanteen=(d)=>
    let 
    schools=List.Distinct(school[School]),
    nc=Text.Combine( List.Difference(schools,List.Distinct(Table.SelectRows(school, each d>[Start] and d<[Finish])[School])), ",") 
    in nc,

    Source = Table.FromValue(notCanteen),
    #"Added Custom1" = Table.AddColumn(Source, "Day", each {1..31}),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Month", each {1..12}),
    #"Expanded Day" = Table.ExpandListColumn(#"Added Custom2", "Day"),
    #"Expanded Month" = Table.ExpandListColumn(#"Expanded Day", "Month"),
    #"Added Custom" = Table.AddColumn(#"Expanded Month", "nic", each [Value](#date(2020,[Month],[Day]))),
    #"Filtered Rows" = Table.SelectRows(#"Added Custom", each ([Day] = 2) and ([Month] = 1))
in
    #"Filtered Rows"

in this query you (or you user) can filter the day and the month and get the school list desidered (peraphs)

[Anonymous]

You can add a column with this data using the function and in this way the user can filter as he want