Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi.
I have a table which shows a school, the type of canteen(s) it has, and also when the type of canteen started and stopped.
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
F | 01.01.2019 | 15.01.2019 | 3
I need to show and display several things when slicing. Preferably on the same page but showing it on several pages is okay.
When slicing on dates and the type of canteen I need to have shown three things:
- Schools which have the given canteen type in the specified date range.
- Schools which do not have the given canteen at all at the specified date range.
- Schools which do not have a canteen at all at the specified date range.
I have created three scenarios, and which school should be shown in those scenarios.
I hope you can help me!
Kind Regards Louise.
Solved! Go to Solution.
@Anonymous
Load table "t1"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTq0QMHAUA+IjAyMDIAcGAJKKRiaoktANGBDhkqxOhgmGlriMBEhQaKJGG40NtAzMCbSjUZgE52o7kZnoAIoAnFdcDoZKIEZpngsMEIzz9AI1cEIi4yICgKIc11RneuGMzywh4YCLtONlWJjAQ==", 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"
Create a table:"Date"
Date = CALENDAR(DATE(2018,1,1),MAX(t1[Finish]))
Create another table :"School"
School = DISTINCT(SELECTCOLUMNS(t1,"School",t1[School]))
Measure1: Case1
Case1 =
var t2=CALCULATETABLE(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])))),ALL(t1[Type]))
var t3=CALCULATETABLE(SELECTCOLUMNS(t1,"sc",t1[School],"Flag",IF(ISBLANK(t1[Finish]),FALSE(),t1[Type]= SELECTEDVALUE(t1[Type]))),ALL('Date'[Date]))
return
SWITCH(TRUE(),SELECTEDVALUE(School[School])in SELECTCOLUMNS(filter(t2,[Flag]=TRUE()),"sc",[sc]) && SELECTEDVALUE(School[School]) in SELECTCOLUMNS(filter(t3,[Flag]=TRUE()),"sc",[sc]),1,0)
Measure 2: Case2
Case2 =
var t2=CALCULATETABLE(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])))),ALL(t1[Type]))
var t3=CALCULATETABLE(SELECTCOLUMNS(t1,"sc",t1[School],"Flag",IF(ISBLANK(t1[Finish]),FALSE(),t1[Type]= SELECTEDVALUE(t1[Type]))),ALL('Date'[Date]))
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)
Measure 3: Case3
Case3 =
var t2=CALCULATETABLE(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])))),ALL(t1[Type]))
VAR t3=FILTER(t2,[Flag]=TRUE())
--SWITCH(TRUE(),SELECTEDVALUE(School[School]) in SELECTCOLUMNS(filter(t2,[Flag]=TRUE()),"sc",[sc]),1,0)
Return
SWITCH(TRUE(),NOT(SELECTEDVALUE(School[School])) in SELECTCOLUMNS(filter(t2,[Flag]=TRUE()),"sc",[sc]),1,0)
These cases will be pulled as a visual fiter in School table (Pulled thrice as a visual): is equal to 1 or greater than 1
@Anonymous So this is basically a complex selector. https://community.powerbi.com/t5/Quick-Measures-Gallery/The-Complex-Selector/m-p/1116633#M534
You will want a disconnected Date table.
Measure1 =
VAR __Min = MIN('Date'[Date])
VAR __Max = MAX('Date'[Date])
VAR __Table = FILTER('Table',[Start]>=__Min && [Finish] <=__Max)
RETURN
COUNTROWS(__Table)
Measure2 =
VAR __Min = MIN('Date'[Date])
VAR __Max = MAX('Date'[Date])
VAR __Type = MAX('Table'[Type of canteen])
VAR __Table = FILTER(ALL('Table'),[Start]>=__Min && [Finish] <=__Max && [Type of canteen]<>__Type)
RETURN
COUNTROWS(__Table)
Measure3 =
VAR __Min = MIN('Date'[Date])
VAR __Max = MAX('Date'[Date])
VAR __Type = MAX('Table'[Type of canteen])
VAR __Table = FILTER(ALL('Table'),[Start]>=__Min && [Finish] <=__Max && ISBLANK([Type of canteen]))
RETURN
COUNTROWS(__Table)
You might need something like Open Tickets though. https://community.powerbi.com/t5/Quick-Measures-Gallery/Open-Tickets/m-p/409364#M147
Thank you for the response. Does the last
RETURN
COUNTROWS(__Table)
at each measure, give me the number of schools?
I would like to have explicitly the schools showed and not only the number of schools.
Kind Regards
Louise
@Anonymous Try
RETURN
CONCATENATEX(__Table,[School],",")
I altered the measure such that
@Anonymous I would need sample data as text to mock this up and test. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
The table looks like this.
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.01.2019 | 01.02.2020 | 1 |
E | null | null | null |
F | 01.01.2019 | 15.01.2019 | 3 |
In my original post I showed what I expect from the sample data (with different examples).
Unfortunately, I do not know how to get from 1 to 2, as I have tried several things. Do you have any ideas?
I hope you might find a solution.
Kind Regards Louise.
@Anonymous
Load table "t1"
let
Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTq0QMHAUA+IjAyMDIAcGAJKKRiaoktANGBDhkqxOhgmGlriMBEhQaKJGG40NtAzMCbSjUZgE52o7kZnoAIoAnFdcDoZKIEZpngsMEIzz9AI1cEIi4yICgKIc11RneuGMzywh4YCLtONlWJjAQ==", 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"
Create a table:"Date"
Date = CALENDAR(DATE(2018,1,1),MAX(t1[Finish]))
Create another table :"School"
School = DISTINCT(SELECTCOLUMNS(t1,"School",t1[School]))
Measure1: Case1
Case1 =
var t2=CALCULATETABLE(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])))),ALL(t1[Type]))
var t3=CALCULATETABLE(SELECTCOLUMNS(t1,"sc",t1[School],"Flag",IF(ISBLANK(t1[Finish]),FALSE(),t1[Type]= SELECTEDVALUE(t1[Type]))),ALL('Date'[Date]))
return
SWITCH(TRUE(),SELECTEDVALUE(School[School])in SELECTCOLUMNS(filter(t2,[Flag]=TRUE()),"sc",[sc]) && SELECTEDVALUE(School[School]) in SELECTCOLUMNS(filter(t3,[Flag]=TRUE()),"sc",[sc]),1,0)
Measure 2: Case2
Case2 =
var t2=CALCULATETABLE(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])))),ALL(t1[Type]))
var t3=CALCULATETABLE(SELECTCOLUMNS(t1,"sc",t1[School],"Flag",IF(ISBLANK(t1[Finish]),FALSE(),t1[Type]= SELECTEDVALUE(t1[Type]))),ALL('Date'[Date]))
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)
Measure 3: Case3
Case3 =
var t2=CALCULATETABLE(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])))),ALL(t1[Type]))
VAR t3=FILTER(t2,[Flag]=TRUE())
--SWITCH(TRUE(),SELECTEDVALUE(School[School]) in SELECTCOLUMNS(filter(t2,[Flag]=TRUE()),"sc",[sc]),1,0)
Return
SWITCH(TRUE(),NOT(SELECTEDVALUE(School[School])) in SELECTCOLUMNS(filter(t2,[Flag]=TRUE()),"sc",[sc]),1,0)
These cases will be pulled as a visual fiter in School table (Pulled thrice as a visual): is equal to 1 or greater than 1
Hi @Anonymous
Thank you so much for your solution! It worked perfectly for my small example.
However, for my greater example, it did not work. It did find a pattern, where measure 3 (Case3) did not work.
When a school has 2 canteens where the finish dates are the same, measure 3 (Case 3) does not show the right schools. I added 2 rows for this to be shown.
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.01.2019 | 01.02.2020 | 1 |
E | null | null | null |
F | 01.01.2019 | 15.01.2019 | 3 |
G | 01.01.2020 | 31.03.2020 | 3 |
G | 01.01.2020 | 31.03.2020 | 3 |
When these two rows are added Measure 3 does not display, what it should (in scenario 2). The following screenshot shows this.
(My Power BI is danish)
In Scenario 2, case 3 (Schools which do not have a canteen at all at the specified date range), Measure 3 shows School G which does have a canteen at the specified date range.
I hope you can help me again.
Kind Regards Louise.
Thank you for the response.
Changing the measure does not give me exactly what I want. I tried the measures with my 3 scenarios and this is what I got:
Did it work for you?
Kind Regards Louise.
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |