Tables of data

[Sun1]

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

[v-huizhn-msft]

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

[Greg_Deckler]

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.

[Sun1]

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

[v-huizhn-msft]

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

[Sun1]

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

[v-huizhn-msft · Accepted 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

[Sun1]

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