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 All,
Slightly difficult to word this but here goes...
I have tables of data going as far back as 1999/2000. Each table differs in that some of the data items are not consistent through the years, i.e. the data items get added or removed. The organisation code data remains consistent and is seen as the identifier and as the key to build relationships in BI. What I want is to emulate the website below, where the user can select reports to download based on what they chosen from the drop down filters. i.e Year, Level and Section. (For now I'm trying to achieve All Trust and All Site.)
Is this possible with the current functionality of BI?
http://hefs.hscic.gov.uk/ReportFilter.asp
Advice would be appreciated, feel free to ask questions.
Sun1
Solved! Go to Solution.
Hi @Sun1,
Based on your description, you want to filter all the fields(column hearder in Trust Level table). We are unable to achieve it, because the slicer is used to filter the row records in the table rather than columns. Thanks for understanding.
Best Regards,
Angelia
Hi @Sun1,
You can create the report in Power BI desktop using any native visualization or custom visual. For the drop down filters, i.e. Year, Level and Section in the web, you'd better create three slicers in the report. One year slicer, one Level slicer and another slicer including Section filter. More details about slicer, please review this article. Just like the report in the article, it will return different report when you select the different values in slicer, which is similar in the website report.
If this is what you want, please post the sample data and expected result for further analysis.
Best Regards,
Angelia
Can you provide some sample data from a couple different years, the solution is going to depend on your data and I like to try to replicate this to find the solution. Text data please, screen shots make me type.
Smoupre
Below are 4 tables thats have been editied, 2 tables are at Trust Level and 2 are at Site Level (NB. Each Trust has a number of sites). As a user I should be able to select the year and then the level, Trust or Site to display a full table showing all data items. The concept is straight forward but unsure how to work it out.
Like the link below.
http://hefs.hscic.gov.uk/ReportFilter.asp
Thanks once again.
1999/2000 Site Level data
Organisation Code | Organisation Name | Commissioning Region | Organisation Type | Year | Available Beds (No.) | Site footprint (m²) | Occupied Floor Area (m²) | Unoccupied Floor Area (m²) | Leased In Floor Area (m²) | Leased Out Floor Area (m²) | Site Land Area (Hectare) |
RA4 | EAST SOMERSET NHS TRUST | Dorset & Somerset | SMALL ACUTE OUTSIDE LONDON | 1999/2000 | 406 | 19,594 | 37,290 | 266 | 840 | 109 | 6.1990 |
RAX | KINGSTON HOSPITAL NHS TRUST | South West London | MEDIUM ACUTE LONDON | 1999/2000 | 562 | 20,753 | 58,331 | 0 | 1,074 | 0 | 6.5400 |
RCF | AIREDALE NHS TRUST | West Yorkshire | MEDIUM MULTI-SERVICE | 1999/2000 | 603 | 51,985 | 76,557 | 0 | 278 | 0 | 27.0370 |
2003/2004 Site Level data
Organisation Code | Organisation Name | Commissioning Region | Organisation Type | Year | Percentage of single bedrooms for patients (%) | Number of Nightingale wards in use - Acute (No.) | Number of Nightingale wards in use - Elderly (No.) |
5A1 | NEW FOREST PCT | Hampshire & Isle of Wight | PCT | 2003/2004 | 0.34% | 0 | 0 |
5A2 | NORWICH PCT | Norfolk, Suffolk and Cambridgeshire | PCT | 2003/2004 | 0.66% | 0 | 0 |
5A3 | SOUTH GLOUCESTERSHIRE PCT | Avon, Gloucestershire & Wiltshire | PCT | 2003/2004 | 0.00% | 0 | 0 |
1999/2000 Trust Level data
Organisation Code | Organisation Name | Commissioning Region | Organisation Type | Year | Estates Service Costs (£) | Total number of staff employed (WTE) | Telecommunication service cost (£) | Average Response Time (Sec's) | RIDDOR incidents (No.) | Non-RIDDOR incidents (No.) | Sterile Services cost (£) |
R01 | ASHWORTH HOSPITAL AUTHORITY | Cheshire and Merseyside | SPECIALIST | 1999/2000 | 1,065,023 | 1,612 | 193,131 | 4 | 46 | 319 | |
R02 | BROADMOOR HOSPITAL AUTHORITY | North West London | SPECIALIST | 1999/2000 | 1,573,817 | 1,066 | 114,774 | 36 | 51 | 303 | |
R03 | RAMPTON HOSPITAL AUTHORITY | Trent | SPECIALIST | 1999/2000 | 157,050 | 1,242 | 136,167 | 5 | 0 | 0 |
2003/2004 Trust Level data
Organisation Code | Organisation Name | Commissioning Region | Organisation Type | Occupied beds (No.) | Available beds (No.) | Total number of wards (No.) | Year | Number of sites - General Acute Hospital (No.) | Number of sites - Multi-service Hospital (No.) | Number of sites - Short Term Non-Acute Hospital (No.) | Number of sites - Long Stay Hospital (No.) |
5A1 | NEW FOREST PCT | Hampshire & Isle of Wight | PCT | 137 | 140 | 34 | 2003/2004 | 0 | 1 | 4 | 0 |
5A2 | NORWICH PCT | Norfolk, Suffolk and Cambridgeshire | PCT | 275 | 320 | 35 | 2003/2004 | 0 | 0 | 2 | 1 |
5A3 | SOUTH GLOUCESTERSHIRE PCT | Avon, Gloucestershire & Wiltshire | PCT | 2003/2004 | 0 | 0 | 0 | 0 |
Hi @Sun1,
For Trust Level, create a slicer, which field should added? By another way, like the choose in the web, what value should be as the options? Please describe clearly. Thanks a lot.
Best Regards,
Angelia
Hi Angelia,
The Year and Level would be slicers that a user would select. If I was to select 2003/2004 and then select Trust Level I would like the table to appear with all fields. The website shows another dropdown, 'Section', but in my case I only want to change between Trust Level data and Site Level data. In terms of how to do this and to see if it's possible, I'm unsure.
I hope that explains it. Sorry I can't explain it further.
Sun1
Hi @Sun1,
Based on your description, you want to filter all the fields(column hearder in Trust Level table). We are unable to achieve it, because the slicer is used to filter the row records in the table rather than columns. Thanks for understanding.
Best Regards,
Angelia
Cheers for that Angelia, I was wondering whether it was possible or not. I may try think of some alternatives. But thanks again for your advice.
Sun1
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.
User | Count |
---|---|
97 | |
94 | |
74 | |
71 | |
64 |
User | Count |
---|---|
143 | |
109 | |
103 | |
82 | |
74 |