Search code examples
powerbidaxpowerbi-desktopdaxstudio

Is there a way to conditionally calculate numeric fields based on string values in DAX (Power BI)?

I have an interesting challenge:

I have 19 metrics which may or may not have values for each facility each month.

I need to have a date filter and facility filter for my users.

Each facility (rows) has a total capacity.

Assuming a user has selected 5 facilities, the formula should be based on sum of the metric / sum of total capacity of selected facilities

My problem is in calculating the denominator (sum of total capacity of these facilities) which needs to be based on the following conditions:

  1. All facilities in the user's filter selection
  2. Exclude facility if it has a null or blank numerator (sum of metric) (and of course, exclude if it has data but user hasn't selected it.)
  3. Dynamically calculate based on month (some months have data... others do not)

It must dynamically calculate because I have 19 metrics x 21 facilities x months!

At the moment, my formula just calculates total capacity of all selected facilities, and I haven't found a way to exclude if the numerator is null / blank.

In terms of table structure, I get the values of metrics and dates from one table (Raw Data) and the total capacity from another table (Operations).

Note: 0 is a valid response for the metric and will be included.

Let's focus only on the denominator. I've tried a bunch of things to a) try to get the correct list of facilities b) give me the sum of the facility capacity. Here are just a small bunch of things I've tried:

Code 1: IF( CALCULATE( SUM('Raw Data'[Metric 1])>=0,ALLSELECTED('Raw Data'[Facility])),VALUES('Raw Data'[Facility]),BLANK())

Code 2: FIRSTNONBLANK(TOPN(1,VALUES('Raw Data'[Facility]),SUM('Raw Data'[Metric 1])>=0,ASC),1)

Code 3: CALCULATE([Total Capacity],SUM('Raw Data'[Metric 1])>=0,Operations[Facility] in ALLSELECTED('Raw Data'[Facility]))

Solution

  • Thanks for the data! That explains a LOT!! The good news is the solution is really straightforward - the Operations file is fine, but the other one, Raw Data, needs to be unpivoted, which is super easy. Once you import it, you can transform it or load it... or right-click on the table, pick Edit Query from the menu... takes you to new screen... (you'll see the table). Select the Date and Facility or whatever columns and then unpivot the rest. Then you get (Attribute, Date, Facility, Value)... {I'm guessing... I don't really understand what the data means!}. Then you can filter out any records where Value = Null. THEN you can do things with it. Like creating useful measures... I'm assuming "Value" (the numeric value) is some kind of count. I'm assuming this is counts of people, so I created a measure

    Total Headcount = SUM('Metrics'[Value]).

    and one from Facility (I assume that's what it is... the other table)

    Total Capacity = SUM('Facility'[Capacity])

    So finally, I add a Line and Stacked Column Chart to my design surface, and [Total Headcount] to values or whatever and rows is Date. Oh and ummm... Capacity to the line... then I can see when I'm over capacity.

    Was that your question?