CALCULATE value using IF statement based on a SELECTEDVALUE

Power BI
1

Hello,

this is my first post on the forum and I’m looking for help with returning a calculated value.
The value I need is the manual hours saved.
I have a fact table of all transactions with [Status] and [Category] columns, and a dimension table of categories with a [manual minutes] column.
The calculation is ( # of “Successful” transactions ) * ( manual minutes of the SELECTED [Category] )/ 60
I attempted to create a parameter for the manual minutes, which works to show individual categories, but I need to show multiple categories if selected.
Maybe this sum needs to be weighted? Not sure

please advise,

2

@Schreg ,

Welcome to the forum - great to have you here!

What I think you’re describing sounds totally doable, but without a PBIX file and a mockup of the results you want to acheive it’s difficult to provide a specific solution. If you can please provide those, I’m conflident we can provide you a solution quickly. Also, keep in mind that many issues that people pose as DAX problems are in fact data modeling problems, which is another reason seeing your PBIX file would be very helpful.

In the interim, based on my understanding of your requirement, I think you may find this video relevant and useful:

  • Brian
1 Like
3

Hi @BrianJ, Thanks so much for the quick response and the YouTube video. I am watching it right now. I would like to send over the PBIX file and a mock of the results I want to achieve, but the data is sensitive (as it is customer data). How do you recommend I create the file without the sensitive data?

4

@Schreg ,

I’m glad you asked… :grinning:

I’ve put together the following post with three different approaches for providing masked or representative data to the forum without compromising any confidential/sensitive information. At least one of these definitely should work for you. Just give a shout if you have any questions.

https://forum.enterprisedna.co/t/tip-tools-and-techniques-for-providing-pbix-files-with-your-forum-questions/17763

= Brian

1 Like
5

Hi @BrianJ, here’s my PBIX file:
hoursSaved_test.pbix (140.9 KB)

6

@Schreg ,

Thanks for the PBIX file - turns out to have been critical, since this ended up being both a DAX problem AND a data modeling problem.

A few preliminary things:

  1. For any type of time intelligence problem, you need to make sure your Date table is marked as a date table. I went ahead and marked yours for you.
  2. Generally, you want to aggregate on your fact tables (e.g., SUM, COUNTROWS, AVERAGE, etc.) and filter/slice on your dimension tables. Thus, what you ultimately should do here is to create a full-blown Category dimension table with Category Code, Category Description, etc. In lieu of that for now, I just created a disconnected category table for slicing.
  3. The Manual Minutes table is not a dimension table that should be connected to your data model. Instead, it is a helper table that should not have a physical connection to the data model.

Here’s the approach I took:

First, created a measure to count the number of successful actions by selected category:

Num Successful Actions = 

VAR Result =
CALCULATE(
    COUNTROWS( 'Fact Table' ),
    FILTER(
        ALLSELECTED('Fact Table' ),
        'Fact Table'[Category] = SELECTEDVALUE('Disconn Category'[Category] ) &&
        'Fact Table'[Status] = "Successful"
    )
)
RETURN
COALESCE( Result, 0 )

Note that I used the COALESCE function at the bottom, because I think it looks bad in a card visual to have the result be “BLANK( )”, so this forces blanks to 0s.

Then I used LOOKUPVALUE() to look up the minutes saved value for the selected category and multiplied that by the number of successful actions, then divided the whole thing by 60 to get hours saved:

Manual Hours Saved = 

VAR LookupManMinutes = 
LOOKUPVALUE(
    'Manual Time'[Manual Minutes],
    'Manual Time'[Category Name],
    SELECTEDVALUE('Disconn Category'[Category])
)
VAR ResultMin =
[Num Successful Actions] * LookupManMinutes

VAR ResultHrs =
DIVIDE( ResultMin, 60, BLANK() )

Return 
COALESCE( ResultHrs, 0 )

Here’s what it looks like all put together:

image
image1573×676 28.9 KB

I hope this is helpful. Full solution file attached.

2 Likes
7

Very insightful, @BrianJ, thank you so much for this. Would love to know how we can summarize ‘manual hours saved’ across all the categories?

9

@Schreg ,

Okay, to summarize manual hours saved across all categories in a card visual, we have to first strip off any filters on the Category field with the ALL function. We can then use that one column table to build out the rest of our virtual table that we build within the vTable variable using the ADDCOLUMNS function. The @ManualHrsSav column is our prior Manual Hours Saved measure calculated against the evaluation context of every category, filtered by the selected fiscal week. The SUMX function just takes that virtual table and adds up all the values in the [@ManualHrsSav] column. Here’s the measure that does all that:

   Hours Saved All Categories = 

VAR HarvestFW = SELECTEDVALUE('Date'[Fiscal Week] )
VAR vTable =
CALCULATETABLE(
    ADDCOLUMNS(
        ALL(Categories[Category] ),
        "@NumSuccess", [Num Successful Actions],
        "@ManualHrsSav", [Manual Hours Saved]
    ),
    'Date'[Fiscal Week] = HarvestFW
)

VAR Result =
SUMX(
    vTable,
    [@ManualHrsSav]
)

RETURN Result

image
image1606×1161 42 KB

Note: rather than provide you a stopgap solution using the disconnected DAX calculated category table, I just built out the actual dimension table manually and incorporated it properly into your data model.

I hope this is helpful, and the explanation clear. Full revised solution file attached.

1 Like
10

This is really great @BrianJ, it allows me to see ManHrsSaved across all selected categories by week and day. Which is exactly what I was looking for. But I don’t understand what the values represent when no week is selected. I would imagine it should represent ManHrsSaved across all categories across all time, however, it doesn’t seem to be the case as that value should be much greater than what it is showing. Any ideas?

11

@Schreg ,

OK, knowing that FW may not be selected is helpful and only requires a couple of small changes to our prior measure:

image
image1116×1109 81.5 KB

The other thing I noticed is that there was a value for (Blank) in your slicer. This is always a big red warning light that there might be a referential integrity violation (i.e., value in your fact table that does not exist in your dimension table), which was the case. You had set up your Date table for October 2021 only, while there were Sept 2021 values in the fact table. This is part of the reason why your Date table should alyways be composed of entire years. Once that was fixed and revised measure in place, this runs like a champ…

image
image1593×1155 44.4 KB

Revised solution file attached (hopefully third time’s a charm…)

12

Hello @Schreg, just following up if the response from @BrianJ help you solve your inquiry?

If not, how far did you get and what kind of help you need further? If yes, kindly mark as solution the answer that solved your query.

13

Hi @Schreg, we’ve noticed that no response has been received from you since October 27.

We just want to check if you still need further help with this post? In case there won’t be any activity on it in the next few days, we’ll be tagging this post as Solved.

14

Query Solved, Thanks!