Counting Across N number of columns

DAX DAX Calculations
1

So I thought I asked this question but I may have forgot to hit submit on the topic before closing the browser.

So in the attached sample file I give an example of a typical student and the multitude of Academic Plans they could have along with Sub-Plans. What I want to be able to do is count the number of students that by the Plan or Sub-Plan name. So if the student has something in Column C through Column AF I want to count it by what it is specifically.

So my ideal visual output would be the following where the Plan and Sub Plan are listed regardless of whether it is 1, 2, 3, 4, 5, etc and the count of unique student that has that program listed for them.

2018-05-30_11-16-48

The one complication is that there is no guarantee that the columns will always be Column C through AF. So is there a way to create a formula that says if the column has this name in a series of IF statements so I catch all applicable columns?

Sample File for EDNA.xlsx (8.4 KB)

2

***Side note - can you please add topics to your posts around what it is actually about. I’ve updated for this one.

3

You need to unpivot this table you’ve added so that all the headers from “Plan 1 Description” onwards are in one column and all the results are also in one column.

Power BI works over long and thing table (well DAX does anyway).

There’s actually no efficient way to do what you have requested based on the way the table is currently set up.

Once you unpivot this, the answer is very very simple and all you need is a COUNTROWS function.

You may also need to break out the plans into a lookup table as well.

This example is a good one where setting up the model right makes you life incredibly easy. In this case the transformations are not too difficult with just the unpivot.