Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Slicing and showing several things

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.

Untitled2.png

I hope you can help me!

 

Kind Regards Louise.

1 ACCEPTED SOLUTION
Anonymous
Not applicable

@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
Capture.PNG

View solution in original post

10 REPLIES 10
Greg_Deckler
Super User
Super User

@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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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

@Anonymous Try

RETURN

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


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

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
Untitled4.png
 
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. 

@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.


@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Mastering Power BI 2nd Edition

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Hi @Greg_Deckler 

 

The table looks like this.

A01.01.202015.01.20201
A01.01.201915.01.20191
A01.01.202030.03.20202
B01.01.201915.01.20191
Cnullnullnull
D01.01.202015.01.20202
D01.01.201901.02.20201
Enullnullnull
F01.01.201915.01.20193

 

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
Not applicable

@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
Capture.PNG

Anonymous
Not applicable

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
Anonymous
Not applicable

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.

 

A01.01.202015.01.20201
A01.01.201915.01.20191
A01.01.202030.03.20202
B01.01.201915.01.20191
Cnullnullnull
D01.01.202015.01.20202
D01.01.201901.02.20201
Enullnullnull
F01.01.201915.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.

Measure 3.PNG 

(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.

 

 

 

Anonymous
Not applicable

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:

Untitled3.png

 

Did it work for you?

 

Kind Regards Louise.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors