I will receive all criticism and suggestions gladly.
I’m eager to see the metrics that others devised. To me, it seemed that they were very basic.
I concede that colors and navigation are topics I need to address.
Regards,
John Giles
I will receive all criticism and suggestions gladly.
I’m eager to see the metrics that others devised. To me, it seemed that they were very basic.
I concede that colors and navigation are topics I need to address.
Regards,
John Giles
Truly sorry to hear that and my thoughts and prayers are with you and your family. Wishing you all well.
Appreciate the determination and courage to participate.
Looking forward to seeing you on the next one.
If there is anything at all I can do to support in the interim do not hesitate to ask.
Best Wishes,
Haroon
Brian:
I don’t know if this is acceptable, but I found a “rogue” shape while taking one final spin through my PBIX.
I’ve attached the version after I removed it.
John Giles
Challenge 14 - John Giles.pbix (730.6 KB)
I just recorded a live stream on Enterprise DNA TV, working through my submission.
I had to cut it short so this is just PART 1. Might finish up part 2 tomorrow
Emergency Services Insights - Completed Model.pbix (329.2 KB)
Also above is a more refined model, if you want a head start on your own development. You can use my model to skip a lot of the query editor and data model work. Not all but most of it.
Chrs
Sam
@JohnG ,
Sure - that’s no problem. We definitely don’t want rogue shapes wandering around the Challenge unchecked…
Everyone,
Attached is my submission. Wanted to get this out before heading out on vacation.
Challenge 14 (Weinzapfel).pbix (8.3 MB)
(@BrianJ - if you are able to publish this, that would greatly appreciated)
Excited to have caught this one having missed #13. With every challenge, I usually set a couple of goals/new skills to accomplish. For this one:
I wanted to create a visual using the “Charticulator” custom visual. This is by far my favorite custom visual yet requires some learning curve. So this was a great opportunity to use this. [Visuals are on 3rd picture below; chart on right side - there are actually 3 in total although two are toggled between bookmarks]
(@sam.mckay - perhaps a training series on Charticulator?)
Given the time data – I wanted to use the “Timeline by Queryon” custom visual (credit to Brian Julius for showing this previously)
Also - with most of my visuals now going forward, I always try and add a help/information view that Guy in a Cube showed a while back (example in last picture below). And - with every report that I do - some tooltips added in here and there.
ETL Notes
Report Features
Overall:
• Slicer panel on every page; Panel appears/disappears to save space. All slicers are synced across the pages; Filters applied are shown on the top right of each page
• Each page has an information sheet. On the left side navigation bar, there is a question mark that will toggle this (credit for this technique goes to Guy in a Cube)
Volume Analysis:
• Call Volume by month will show min and max amounts (technique was one from a WoW challenge - http://www.workout-wednesday.com/power-bi-challenges/)
• Ambulance/Hospital matrix has tooltips to provide more detailed information
Time Analysis:
• “Charticulator” used to show averages by each segment (right side visual); there are 3 different visuals here with the top one being an overall average and then a section below to show breakdown by station. There are buttons to toggle between total time elapsed versus the individual segment averages for this lower section.
Patient Page:
• “Timelime by Queryon” used to display timeline of call. This only works well if one patient is selected. Thus a message is displayed when not. Technique for this credited to Excelerator BI (https://exceleratorbi.com.au/show-or-hide-a-power-bi-visual-based-on-selection/)
Power BI Tips Youtube Channel for great videos on Charticulator https://www.youtube.com/channel/UCPwPrIpZwlfIKcoUpRwl9OQ
https://www.iconsdb.com/white-icons/ for all the icons in the report.
Love your submission! Prior to seeing your submission, my model design was similar. I added an index to the fact table and focused on the response time segments in my reporting. However, I really like the Timeline by Queryon that you used vs the column chart that I used. Great job!
Absolutely dynamite entry! Really well-organized – provides a wealth of information without overwhelming the viewer. Great use of a wide range of creative visualization techniques, including an excellent application of QueryOn Timeline (a personal favorite…). Also really like the incorporation of the GIAC help overlay,
All-around great submission.
Here’s the Publish to Web link:
Re: your suggestion on an Enterprise DNA series on Charticulator - we got you covered. Check out @MudassirAli 's excellent Advanced Visualization Techniques platform course part two – the entire course focuses on developing custom visualizations using Charticulator.
Have a great vacation!
All,
Per @tweinzapfel 's message above, if you don’t have Publish to Web capability but would like your report published, just let me know and I’m happy to publish it via the Enterprise DNA tenant and send you to PTW link to add to your post/writeup.
Hi Brian!
Can you publish my submission to the web? I submitted via the email address earlier today.
Thanks! DianaWill
@BrianJ - thanks for publishing and the great feedback! And I’ll be sure to check out that video series (I’m vastly behind!).
@DianaWill - definitely looking forward to seeing yours.
Thank you
Happy to do so, but would you mind sending another copy of your PBIX to brian.julius@enterprisedna.co ?
(I don’t have yet have access to the main submission inbox for the challenges)
Thanks for participating! Looking forward to seeing your entry.
Thanks Brian! Email is on the way!
Diana
Sorry to ask this again, but I am ok to submit my report Sunday evening UK time?
As a regular “keep tinkering right up to the deadline” Challenge Guy, I can tell you that as long as you submit before it hits midnight on July 11 in Los Angeles, you’re good to go.
SUBMISSION DUE DATE - Sunday, 11th July 2021 (PST)
– Brian
@FPastor ,
Welcome to the Challenges! This is a terrific first entry.
I really like the dark background theme, and the organization and navigation makes for an excellent user experience. You’ve added some really nice UI touches (I particularly like the “bounce” when you mouse over the main page buttons). Very effective use of the donut charts – I really like including the target value in the center. I think the heat maps are a very effective way of conveying the information you are depicting (one suggestion there – I really like the single color approach on that page, but I would’ve preferred a wide range of hues in the heat map to make the differences in values “pop” a bit more.)
Overall, a really well-conceived, well-executed entry.
Thanks for participating!
– Brian
Thank you Brian for the feedback. I do agree with you on the heatmaps 1 colour choice.
FP1967
Hi all. Here’s my submission for Data Challenge 14, Emergency Services Analytics. I chose to concentrate on the technical infrastructure rather than focus on the ultimate utility of the final report, and proceeded with the following goals:
Unfortunately, it looks like Publish-to-Web is not supported when using shared datasets:
Pillar | ID | Best Practice | Notes |
---|---|---|---|
Pre-Development Setup | 1 | Auto date/time: Disable | Done |
2 | Auto detect relationships: Disable | Done | |
3 | Use cross filtering instead of cross highlighting: Enable | Done | |
4 | Separate dataset from report | Done | |
Data Loading and Transformation | 5 | Use dedicated [Dates] table and mark as a date table | Done |
6 | Source: Use existing report/dataset before developing new | N/A | |
7 | Stage and reference data | Done | |
8 | Performance - Data Volume: Reduce as much as possible | N/A | |
9 | Performance - Query Folding: Enable as much as possible | N/A | |
10 | Performance - Data Connectivity Mode: Use Import if possible | Done | |
11 | Location/Shape | N/A | |
12 | Naming/Data Types | Done | |
13 | Granularity: Strive to source data at coinsistent granularity | N/A | |
Data Modelling | 14 | Star Schema | Done |
15 | Waterfall Layout: Lookup tables above fact tables | Done | |
16 | Waterfall Layout: Measure tables at top-right (columns) | Done | |
17 | Waterfall Layout: Supporting tables at bottom-left (rows) | Done | |
18 | Use Manage Relationships dialog | Done | |
19 | Use Edit Relationships dialog | Done | |
20 | Use 1-to-many relationships/avoid bi-directional relationships | Done | |
21 | Use Active and Inactive Relationships | Done | |
22 | Use Measure Tables | Done | |
23 | Rename linking columns (Key) | Done | |
24 | Column Visibility: hide key, sort | Done | |
DAX Calculations | 25 | Format DAX | Done |
26 | Comment DAX | N/A | |
27 | Variables: Use verbose variable names | Done | |
28 | Variables: Use underscore prefix | Done | |
29 | Variables: Use RETURN _result | Done | |
30 | Qualification: columns are; measures are not | Done | |
31 | Use CALCULATE: To change the context of a calculation | Done | |
32 | Use DATEADD: Most versatile / easy copy-and-paste | Done | |
33 | Use DIVIDE instead of arithmetic division; error handling | Done | |
34 | Use SWITCH TRUE instead of nested IF statements | Done | |
35 | Virtual Tables: Use SUMMARIZE to construct the base table | Done | |
36 | Virtual Tables: Use ADDCOLUMNS to construct the extended table | Done | |
37 | Virtual Tables: Use @ prefix on extended columns | Done | |
38 | Use USERELATIONSHIP: To activate inactive relationships | N/A | |
39 | Measures: Use Measures instead of calculated columns where possible | Done | |
40 | Measures: Use Measure Branching | Done | |
41 | Measures: Use Measure Groups (Measure Tables) | Done | |
42 | Context | N/A | |
43 | Aggregation / Iteration (e.g., SUM, SUMX, etc.) | N/A | |
Reports and Visualizations | 44 | Use FORMAT / GENERAL to adjust X/Y/Width/Height to multiples of 10 px | Done |
45 | Use Model View to turn off summarization | Done | |
46 | Keep Selection pane and Bookmarks pane visible at all times | Done | |
47 | Set format of date columns to dd-mmm-yyyy | Done | |
48 | Set Matrix column width | N/A | |
49 | Think in grids / align visuals | Done | |
50 | Use transparent colours | N/A | |
51 | Use table-first development (TFD) | Done | |
52 | Use Reset Slicers bookmark and button | Done | |
53 | Use Slicer Selection echo area | N/A | |
54 | Use button On Hover behaviour to add interactivity | Done | |
55 | Use textboxes with measures to add dynamic narratives | Done | |
56 | Set the data category for location fields | N/A | |
57 | Use Edit Interactions to modify filter effect on visuals | N/A | |
58 | Use Selection pane to name and order all visuals | Done | |
59 | Identifiers: add report ID, version, version date, last refresh date | Done | |
60 | Performance: Minimize number of visuals per page | Done | |
61 | Performance: Use dropdown slicers in lieu of list slicers | N/A | |
62 | Performance: Collapse filter pane before saving | Done |
Here’s the notes I kept during development:
Protocol =
TRIM(
SWITCH( TRUE(),
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Abdominal" ), "Abdominal",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Allergies" ), "Allergies",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Animal" ), "Animal",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Assault" ), "Assault",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Back" ), "Back",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Breathing" ), "Breathing",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Burns" ), "Burns",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Inhalation" ), "Inhalation",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Cardiac" ), "Cardiac/Respiratory",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Chest Pain" ), "Chest Pain",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Diabetic" ), "Diabetic",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Drowning" ), "Drowning",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Electrocution" ), "Electrocution",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Eye" ), "Eye",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Heart" ), "Heart",
CONTAINSSTRING( 'Dispatch Codes'[Protocol Full], "Exposure" ), "Exposure",
'Dispatch Codes'[Protocol Full]
)
)
C14 Theme.json (1.8 KB)
Contents of JSON Theme file:
{
"name": "C14 Theme",
"dataColors": [
"#D78888",
"#CC6666",
"#C14444",
"#A13636",
"#7E2A2A",
"#5C1F1F",
"#481818",
"#260D0D"
],
"visualStyles": {
"*": {
"*": {
"title": [
{
"fontColor": {
"solid": {
"color": "#FFFFFF"
}
},
"background": {
"solid": {
"color": "#481818"
}
}
}
],
"background": [
{
"color": {
"solid": {
"color": "#481818"
}
},
"transparency": 50
}
]
}
}
},
"textClasses": {
"callout": {
"fontSize": 30,
"fontFace": "Segoe UI Bold",
"color": "#FFFFFF"
},
"title": {
"fontSize": 12,
"fontFace": "Segoe UI Bold",
"color": "#FFFFFF"
},
"header": {
"fontSize": 12,
"fontFace": "Segoe UI Semibold",
"color": "#FFFFFF"
},
"label": {
"fontSize": 10,
"fontFace": "Segoe UI",
"color": "#FFFFFF"
},
"largeLabel": {
"fontSize": 16,
"fontFace": "Segoe UI Bold",
"color": "#FFFFFF"
}
},
"firstLevelElements": "#FFFFFF",
"secondLevelElements": "#FFFFFF"
}
Hi,
Please see below and attached my submission for challenge 14.
Approach I have taken for this submission is to try and make the submission as flexible as possible for the end user/s for each hospital. So I added a slicer to break each page by hospital, so you can see only key figures for that hospital, but adding in text average comparisons and tooltips where you can get an idea of how other hospitals are doing in selected scenarios.
Page 1 - I focused on Call centre stats, such as volume, hours, age range, avg durations and days of the week.
Page 2 - is focused on the ambulance service. Patients Journey from departure of the ambulance, again a breakdown in hours of the day, to identify peaks and gender and age.
Challenges I found, were coming up with relevant visuals for the measure’s created, so reverted to tables more for this challenge. Ended up using protocol numbers instead of names, but tooltips will identify the name for each P 1, P 2 etc… Time, I only saw this challenge a couple days ago, I’m sure if I had more time, I would probably take a very different approach and maybe something a bit more insightful? I think even though I don’t feel I’m reporting on much, my report feels a bit clunky, might be the fonts I decided to use, If I could redo again, I would try and make everything look a bit smoother maybe?
Hugely enjoyed the challenge, and looking forward to seeing other submissions over the weekend Looking forward to the next!
Thanks,
Sam