Advice on how to structure data for variable requirements?

[Anonymous]

Hey everyone,

I'm hoping I can get some advice from you more seasoned data analysts/Power BI experts out there. I have a simple outcome I'm aiming for but I'm not sure how best to lay everything out.

Basically, I'm curious what the best way to lay out a data set that has variable requirements in it would be. Here's an example of what I mean:

Certification Title	Course Name	Helper Column
Power BI Certification	DAX Measures	X
Power BI Certification	DAX Overview	O
OR
Power BI Certification	Reviewing Data	O
Power BI Certification	Structuring Data	O

In the table above, I have a Certification (Power BI Certification). In order to become certified, you have to at least complete the DAX Measures course in the first grouping of courses (denoted by an X in the helper column) OR you can complete at least one of the courses in the next grouping of courses (after the OR in the table).

My question just boils down to two things:

1. Is this an acceptable way to structure data of this nature? The master data source is an Excel file, so that's why I've enlisted the use of a helper column.
2. Speaking of the helper column, my idea behind it was to write a DAX measure that would do something along the lines of "If the helper column has an X, see if that course is completed, otherwise see if at least one of the O courses is complete instead". But in looking at it, I'm not sure if this is the right approach, especially because there are other certifications that have an AND instead of OR (i.e. learners must complete all courses, not either/or).

I'm just looking for some guidance on how best to lay out my data and allow me the flexibility to derive some sort of system that would help Power BI understand which courses HAVE to be complete to be certified.

I'm open to any and all suggestions anyone might have, because at this point I'm stuck with my current layout. Maybe changing the helper column to have a MANDATORY/OR/AND option is better? Not sure, but hoping someone in the community can educate this newbie! 🙂

[nhoward]

HI @Anonymous 

First thoughts...  PBI works best with non-normalised tables.  So keep adding columns for the conditions, rather than thinking of groups of rows as conditions. 

eg

Certification | Course     | Level | Required | Alternative | Optional

PBI Cert       | Measures  | 1       | Yes          |  No             | No

PBI Cert       | Overview  |  1      |  No          | Yes             | Yes

PBI Cert       | Reviewing | 2      |  No          |  Yes            |  Yes

PBI Cert       | Strucuture | 2     |  No           | Yes            | Yes

Then you might be able to use DAX to find out someone who is qualified, by joing the results table.  If some one completed the Required from level 1 OR an Alternative from Level 2.