Slicing Data With Folder Import CSV Files

[allw]

Hi All,

Thanks for any input in advance, I'd imagine this is a pretty niche circumstance.

As a school we have a report that should look something like the below when completed. It is a "simple" dashboard for some of the relevant stats that our school database spits out. We are trying to alter the report so that we can have a slicer for unintiated staff to use that will allow them to open the report, click on what time span (fixed options) they want using a slicer.

Due to limitations in our database software, we can only output summary values without literally outputting the entire database (which is multiple gigabytes and not something most staff should have access to). So I have managed to (using folder import) link a set of predefined timespans:

• Year To Date
• Last 4 Weeks
• Last 2 Weeks
• Last Week
• Current Week To Date

These save a selection of csv files that are then imported. A simplification of the key data within the files are:

• Count of Present Marks
• Count of Absent Marks
• Count of All Marks
• Student ID

When imported there are additional columns added like:

Time Frame (Originally File Name)

Percentage Attendance = IFERROR((Attendance[Present]/Attendance[All])*100,0)​

Persistant Absence = If(Attendance[Percentage Attendance]<90,"Y","N")

I was hoping to use a slicer to filter those columns and mandate that only one date range could be selected at a time to stop overlapping dates causing errors. Obviously, however, this isn't exactly working as you would expect. If I try and use a slicer it ignores my selection. I am also unaware of how to tell powerBI that my data is categorised as I cannot use multiple different columns with different filters. Additionally, I can filter page using "Time Frame" but this defeats the point of making it easy for staff by using a slicer.

Another option would be to split the data on the graphs, but again that would require some alteration to the formulas that I really don't know. We are looking for something like (but with correct values being displayed):

TLDR:

I want to do something like

Percentage Attendance (Displayed) = CountOfPresent WHERE (TimeFrame IS Slicer Value) / CountOfAll WHERE (TimeFrame IS Slicer Value) *100

OR

Percentage Attendance (categories of TimeFrame) = CountOfPresent WHERE (TimeFrame IS category) / CountOfAll WHERE (TimeFrame IS category) *100

(Then display multiple categories on the charts.)

I would imagine the first would be the one power BI would let me do.

Thanks so much!

[lbendlin]

Are you adventurous enough to try for Calculation Groups, or do you want to keep it simple and use the FILTERS() and HASONEFILTER()  functions to look up which selection the user has made and then calculate your measures accordingly?

[allw]

I'll use whatever works, just don't have the knowledge right now to know what the options are...

Thanks though I'll have a look at those!