Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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.
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.
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.
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.
Hi @Anonymous
This worked perfectly! Thank you.
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 : 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
try using this function:
let
notCanteen=(d)=> Text.Combine( List.Distinct(Table.SelectRows(school, each d<[Start] or d>[Finish])[School]), ",")
in
notCanteen
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
Hi @Anonymous
I corrected the script to match you data
have a look at the last post
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
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
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
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.
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
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)...
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
<<..
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.
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)
You can add a column with this data using the function and in this way the user can filter as he want
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.