topic Re: Slicing and showing several things in DAX Commands and Tips

Slicing and showing several things

Anonymous — Mon, 14 Sep 2020 08:20:52 GMT

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.

Re: Slicing and showing several things

Greg_Deckler — Mon, 14 Sep 2020 08:30:09 GMT

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

Re: Slicing and showing several things

Anonymous — Mon, 14 Sep 2020 20:29:56 GMT

Hi Greg_Deckler

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

Re: Slicing and showing several things

Greg_Deckler — Mon, 14 Sep 2020 21:04:14 GMT

Anonymous Try

RETURN

CONCATENATEX(__Table,[School],",")

Re: Slicing and showing several things

Anonymous — Tue, 15 Sep 2020 07:03:41 GMT

Hi Greg_Deckler

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.

Re: Slicing and showing several things

Anonymous — Tue, 15 Sep 2020 07:39:50 GMT

Hi Greg_Deckler

I altered the measure such that

__Min = MIN('Date'[Date]

was set to

__Min = MIN('Date'[Date].[Date]

(also for the max) which of course helped. Now my scenarios display this

Measure1 is almost there except for scenario 1.

Measure2 is also almost there, but will not include the schools which never had a canteen due to the fact that they do not contain 'Start' and 'Finish'.

Measure3 never displays anything due to the same fact as above. They cannot have a blank at 'Type of canteen' and also have a 'Start' and 'Finish' as the whole row is blank.

Kind Regards Louise.

Re: Slicing and showing several things

Greg_Deckler — Tue, 15 Sep 2020 13:25:24 GMT

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.

Re: Slicing and showing several things

Anonymous — Wed, 16 Sep 2020 20:10:22 GMT

Hi Greg_Deckler

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.

Re: Slicing and showing several things

Anonymous — Thu, 17 Sep 2020 15:50:43 GMT

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

Re: Slicing and showing several things

Anonymous — Thu, 17 Sep 2020 15:53:09 GMT

I have posted how to pull the visual filter.

If my answer was helpful, please consider Accept it as the solution to help the other members find it.(Earlier posts also :))

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

Re: Slicing and showing several things

Anonymous — Mon, 21 Sep 2020 07:29:43 GMT

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.