Latest Enterprise DNA Initiatives

Dax calculation to sum based on criteria

kentyler,

thanks for the dax suggestion.

i had to re-write, specifically i had to add the RETURN after the VAR declaration. Unfortunately it returned the same result as my Dax formula. Any other suggestion. Your like mine only returned the sum of the row highlighted below

Contract Review-4

it should have returned the sum for all the following

Contract Review-5

Version 7 sum = 
VAR ReviewNo = MAX('Contract Review'[Review No])
RETURN

CALCULATE(
    SUM('Contract Review'[Hrs to complete]),
    ALL('Contract Review'),
        'Contract Review'[Review No] = ReviewNo,
        'Contract Review'[ Step Status] = "Approved",
        'Contract Review'[Step Number Text] = "Lvl4 Approval",
        'Contract Review'[WF Status] = "Completed")       

Thanks
J

@jprlimey,

Based on my understanding of the question, I believe the measure below works:

Version 5 sum = 
    
VAR Prelim = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
            AND(
                AND(
                    'Contract Review'[Step Number Text] = "Lvl4 Approval",
                    'Contract Review'[Step Status] = "Approved"
                ),
                'Contract Review'[WF Status] =  "Completed"
            ) = TRUE
        )
    )

VAR PrelimBinary =
IF( Prelim > 0, 1, 0)

VAR Result =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hours to Complete] * PrelimBinary
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)

RETURN
Result 

image

I hope this is helpful. Full solution file posted below.

– Brian

eDNA Forum - ALLEXCEPT SUM Solution.pbix (22.0 KB)

Brian,

That is some Dax formula. Yes it worked but now to try to understand the logic. I’m impressed!

Below is the outputted table

Contract Review-6

And the Dax measure

Version 8 sum = 
 VAR Prelim = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
            AND(
                AND(
                    'Contract Review'[Step Number Text] = "Lvl4 Approval",
                    'Contract Review'[ Step Status] = "Approved"
                ),
                'Contract Review'[WF Status] =  "Completed"
            ) = TRUE
        )
    )
VAR PrelimBinary =
IF( Prelim > 0, 1, 0)
VAR Result =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hrs to complete] * PrelimBinary
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)
RETURN
Result

@jprlimey,

It’s a fair amount of DAX, but the logic is not as complex as it might appear:

Prelim - count up the number of records within a contract review number that meet all three of your conditions

PrelimBinary - if ANY records within a given contract review # meet all those conditions, assign a 1, otherwise assign a 0,

Result - by contract review #, sum the product of (Hrs to Complete * 0/1 PrelimBinary variable) - this effectively sums all the records within a given contract review # where at least one record met the criteria, and zeroes out all the other sums where no records within the contract review #` met all the criteria

The key to this measure is aggregating at the contract review # level, using the ALLEXCEPT function.

I hope this is helpful.

  • Brian

Yes I did notice that the measure returned zero for all contract numbers that didn’t match the criteria. To remove them from the visual I just filtered on values greater than 0, I wonder if adding an if statement inside the Result Variable to check if value > 0, sum those but return BLANK() if = to 0

Ultimately I will be counting the number of contract reviews, as there are numerous types each with different criteria. So your Dax will aid greatly in developing the additional measures. The measures will enable determining the distribution of contract review types and the cycle time to complete each along with average or mean time to complete.

I was prompted or should I say directed to develop an SPC chart (I-MR) to show statistically how we are performing

This forum is absolutely fabulous, it’s amazing what you can learn to broaden your Power BI experience and expertise.

Thanks again to all
J

@jprlimey,

To make life simpler, and avoid having to put the additional visual filter on, just change the PrelimBinary variable in the measure above to this:

VAR PrelimBinary =
IF( Prelim > 0, 1, BLANK())

That will give you the exact result you’re looking for:

image

Revised solution file attached below.

P.S. totally agree with you about the forum – it’s an amazing community.

Brian,

So I’m progressing nicely with my report. But alas i now want to separate results for contract reviews that are not level 2, 3 or 4, these would be classed as level 5.

For level 5 reviews no ‘Contract Review’[Step Number Text] exists detailing they are level 5.
Per the table below you can see that Level 2 thru 4 step status is “Skipped” and the value for Hrs to Complete is #VALUE (none exists).

Contract Review-7

I re-wrote to DAX with and OR statement thinking that would capture when Level 2 thru 4 were status of “Skipped”, the DAX is below

Level 5 Total = 
 VAR Prelim5 = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
            AND(
                AND(
                    OR(
                        OR(
                            OR(
                    'Contract Review'[Step Number Text] = "Lvl4 Approval",
                    ),
                    'Contract Review'[Step Number Text] = "Lvl3 Approval",
                    ),
                    'Contract Review'[Step Number Text] = "Lvl2 Approval",
                    ),
                    'Contract Review'[ Step Status] = "Skipped"
                 ),
                  'Contract Review'[WF Status] =  "Completed"
            ) = TRUE
        )
    )
VAR PrelimBinary5 =
IF( Prelim5 > 0, BLANK(), 1)
VAR Result5 =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hrs to complete] * PrelimBinary5
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)
RETURN
Result5 

I’m getting an error - Argument’2’ in OR function is required.

Any thoughts on the direction to go?

Thanks
J

@jprlimey,

Ouch - that nested AND/OR makes my brain hurt. :smile:

I would suggest breaking it down into a few separate variables that each use SWITCH or SWITCH(TRUE()). This will dramatically simplify the filter condition in your CALCULATE statement and make it easier both to understand and debug.

Give it a go, and shout if you run into problems.

  • Brian
1 Like

All,

As Brian has done a spectacular job in getting this far, i’d like to reach out to anyone else who would like to contribute.

As Brian suggested i tried several versions of DAX to utilize the SWITCH function to overcome the nested AND OR statements, below are 2 that i had most success with, although neither is correct.

Level 5 Test 1 = 
 VAR Prelim = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
             AND(
                SWITCH(TRUE(),
                    'Contract Review'[Step Number Text] = "Lvl4 Approval" && 
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0,                              
                    'Contract Review'[Step Number Text] = "Lvl3 Approval" &&
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0, 
                    'Contract Review'[Step Number Text] = "Lvl2 Approval" &&
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0, 1
                ), 
                    'Contract Review'[WF Status] =  "Completed" 
            ) = TRUE
        )
)
VAR PrelimBinary =
IF( Prelim > 1, 1, BLANK() )
VAR Result =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hrs to complete] * PrelimBinary8
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)
RETURN
Result


Level 5 Test 2 = 
 VAR Prelim = 
    CALCULATE(
        COUNTROWS('Contract Review'),
        ALLEXCEPT(
            'Contract Review',
            'Contract Review'[Review No]
        ),
        FILTER(
            'Contract Review',
            
            AND(
                SWITCH(TRUE(),
                    'Contract Review'[Step Number Text] = "Lvl4 Approval" && 
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0,                              
                    'Contract Review'[Step Number Text] = "Lvl3 Approval" &&
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0, 
                    'Contract Review'[Step Number Text] = "Lvl2 Approval" &&
                    'Contract Review'[ Step Status] = "Skipped" &&
                    'Contract Review'[WF Status] =  "Completed", 0, 0
                ), 
                SWITCH(TRUE(),
                    'Contract Review'[Step Number Text] = "End" &&
                    'Contract Review'[WF Status] =  "Completed", 1,
                    'Contract Review'[Step Number Text] = "Notify CSR Approved" &&
                    'Contract Review'[Step Number Text] = "End", 1, 1)
            ) = TRUE
        )
)

VAR PrelimBinary =
IF( Prelim > 1, 1, BLANK() )
VAR Result =
CALCULATE(
    SUMX(
        'Contract Review',
        'Contract Review'[Hrs to complete] * PrelimBinary9
    ),
    ALLEXCEPT(
        'Contract Review',
        'Contract Review'[Review No]
    )
)
RETURN
Result

In each DAX what i believe i need to do is somehow get the VAR Prelim to be > than 1. as we loop through all the contracts. Where the conditions are TRUE for Level 2 thru 4 “Step Number Text” and the associated “Step Status” is “Skipped”, i assign a 0 value in the SWITCH statement. As the IF statement then checks to see if > 1, and assigns a value of 1 for true and BLANK() for false.

Then for each contract review with the SUMX function we intend to gather the matching values when not a Level 2 thru 4 contract review.

Brian was most helpful in supporting me in getting the correct DAX for contract reviews that were Level 2 thru 4, the last remaining piece is getting the elusive Level 5 number.

The table below shows the results from the DAX Level 5 Test

As you can see contract review no 17778 is in the table. This is a Level 4 contract review and should not be part of the Level 5 table, hence i know i have issues with the DAX.

Contract Review-9

I have also attached a small sample of the database in an Excel format, perhaps this could be uploaded into the pbix file that Brian created, to aid in solving this issue.

Sample Contract Review.xlsx (10.7 KB)

Thanks
J

@jprlimey,

Very considerate, but I’m happy to see this one through to the end with you. I’ve got a big PBI report due tomorrow that takes forever to refresh, so this will give me something productive to do instead of impatiently watching Power Query grind through my Dynamics data over a slow Odata connection…

  • Brian

Brian,

Thank you so much for wanting to push this through to the end. As being a relative newcomer to EnterpriseDNA, I was just tying to respond to the forum prompts when writing my last message, where it encouraged me to get other involved.

Thanks again
J

@jprlimey

No, that’s exactly the right way to handle it. This one has just kind of got me hooked, and I’m enjoying working with you on it.

This iteration was a bit more difficult than the last one, but I think I’ve got it working properly now. Please check to make sure I interpreted the level 5 conditions correctly.


FirstScreen =
AND(
    SELECTEDVALUE( 'Contract Rev2'[Step Number Text] ) IN { "Lvl2 Approval", "Lvl3 Approval", "Lvl4 Approval" },
    SELECTEDVALUE( 'Contract Rev2'[ Step Status] ) = "Skipped"
) 

SecondScreen = IF(
    SELECTEDVALUE( 'Contract Rev2'[Hrs to complete] ) = BLANK(),
    TRUE,
    FALSE)

Level5 Total =
VAR Prelim = CALCULATE(
    COUNTROWS( 'Contract Rev2' ),
    FILTER(
      'Contract Rev2',
        [FirstScreen] = TRUE &&
        [SecondScreen] = TRUE
    ),
    ALLEXCEPT(
      'Contract Rev2',
      'Contract Rev2'[Review No]
    )
) 
VAR AllExTot = CALCULATE(
    SUMX( 'Contract Rev2', [Total Hours2] ),
    ALLEXCEPT(
      'Contract Rev2',
      'Contract Rev2'[Review No]
    )
) 
VAR Result = IF( Prelim = 3, AllExTot, BLANK() ) 

RETURN
Result

image

I hope this is helpful. Full solution file attached below

Please give a shout if you have questions (note: SWITCH also would’ve worked fine for the variables, but in looking at the problem in more detail I found a more efficient construct using IN)

Brian,

I use your DAX and it didn’t work, yes I’m majorly disappointed, as as you most likely, i thought you had it based on your pbix file

I create the separate measures First Screen and Second Screen, just the same as you did in the attached pbix file, below are the measures

First Screen = 
//VAR FirstScreen =
AND(
    SELECTEDVALUE( 'Contract Review'[Step Number Text] ) IN { "Lvl2 Approval", "Lvl3 Approval", "Lvl4 Approval" },
    SELECTEDVALUE( 'Contract Review'[ Step Status] ) = "Skipped"
) 

Second Screen = 
//VAR SecondScreen = 
IF(
    SELECTEDVALUE( 'Contract Review'[Hrs to complete] ) = BLANK(),
    TRUE,
    FALSE
) 

The table outputs are below for each measure

Contract Review-10

Everything up to this point i believe looks good.

I then added the Level5 Total measure, per below.

Level5 Total = 

VAR FirstScreen =
AND(
    SELECTEDVALUE( 'Contract Review'[Step Number Text] ) IN { "Lvl2 Approval", "Lvl3 Approval", "Lvl4 Approval" },
    SELECTEDVALUE( 'Contract Review'[ Step Status] ) = "Skipped"
) 

VAR SecondScreen = 
IF(
    SELECTEDVALUE( 'Contract Review'[Hrs to complete] ) = BLANK(),
    TRUE,
    FALSE
) 

VAR Prelim = 
CALCULATE(
    COUNTROWS( 'Contract Review' ),
    FILTER(
      'Contract Review',
        [First Screen] = TRUE &&
        [Second Screen] = TRUE
    ),
    ALLEXCEPT(
      'Contract Review',
      'Contract Review'[Review No]
    )
) 

VAR AllExTot = CALCULATE(
    SUMX( 'Contract Review',
        [Hrs to Complete All]
    ),
    ALLEXCEPT(
      'Contract Review',
      'Contract Review'[Review No]
    )
) 

VAR Result = IF( Prelim = 3, AllExTot, BLANK() ) 
RETURN
Result

I noticed that the logic for VAR FirstScreen and SecondScreen, were identical to your measuses First Screen and Second Screen in your pbix file, and under the VAR Prelim you reference these actual measures to test if true. So i commented out the FirstScreen and SecondScreen VAr as i felt they were redundant. It still didn’t provide the anything for the table, see below.

Contract Review-11

In the table i’m also showing the measure Hrs to Complete All, i just wanted to make sure it was still returning values. I also added the screen measures and as you see Level5 total is blank to all contract reviews.

The Hrs to Complete All measure is below.

Hrs to Complete All = 
    CALCULATE(
        [Hrs to Complete],
            USERELATIONSHIP('Date'[Date],'Contract Review'[Assign Date])
    )

and the Hrs to Complete measure is below.

Hrs to Complete = SUM (‘Contract Review’[Hrs to complete])

Any idea what may be the issue?

Thaknks
J

@jprlimey,

Sorry - originally I did firstscreen and secondscreen as variables, then in the debugging branched them out as separate measures, but didn’t take the variables out of the Level 5 measure, which I should have to avoid confusion.

I’m currently crunching on a major work deadline. If it’s okay, will take a look back at the solution this evening and see what needs to be done to revise it.

Bummer – thought I had it on the first shot, but will definitely follow up soon to get you what you need.

  • Brian

Brian,

Thank you so much, this is almost as good as one of the Power Bi Challenges… ha ha!

J

@jprlimey,

Okay, I think I finally measure branched this one into submission. Here’s the basic logic:

  1. Created a Both Screens measure, assigning a 1 if First Screen and Second Screen were both true, 0 otherwise
  2. Totaled the Both Screens measure by Contract Review Number
  3. If the total computed above in #2 = 3 (meaning levels 2, 3 and 4 were all skipped), then computed the sum of hours by Contract Review Number, otherwise assigned a blank

Here is all put together. Full solution file posted below.

Please let me know if this gets you what you need – I hope it does.

– Brian

eDNA Forum - Level 5 SUM Solution Rev2.pbix (32.9 KB)

Brain,

it worked, thanks for much for staying with it.

I did notice that I’m unable to get a count, average and sum for Level 5’s per the table image below

In each case i used DAX measures per following

Count Level 5 Total = 
CALCULATE(
    COUNTX('Contract Review', [Level 5 Total])
)

Level 5 SUMX Total = 
CALCULATE(
    SUMX('Contract Review', [Level 5 Total])
)

Average Level 5 Total = 
CALCULATE(
    AVERAGEX('Contract Review', [Level 5 Total])
)

Ironically, when i did a COUNTX on your Level 5 Total measure in the pbix you attached, it worked.

Contract Review-13

Even though we only actually have 2 matching records for the Level 5 Total measure as you can see it returned 18 as the count, which was the total number of records, that matched in your table image, which would be incorrect also under the context shown.

The table below shows Level 4 and Level 5 side by side.

Contract Review-14

the Level 4 DAX measure what the earlier solution you provided. One thing i did notice was that the level 4 column has a totals row, where level 5 doesn’t. Any idea why is can’t get the iteration functions Countx, Averagex, or Sumx to work for Level 5

Thanks
J

@jprlimey,

It’s all about evaluation context. When you drop the measure in the table, the context over which it is evaluating is clear (Review No). However, in a card there is no evaluation context. In case with insufficient evaluation context, DAX will do it’s best to guess, which is why sometimes you get a right answer, sometimes a wrong answer and sometimes a blank, which is DAX’s equivalent of
¯\_(ツ)_/¯.

Thus, for these cards (and for the total row of the table for the Lvl 5 measure), we need to provide the necessary evaluation context within the measure through the use of a virtual table.

image

Here’s the card measure for AVERAGEX:

AverageX Card = 

VAR vTable = 
ADDCOLUMNS(
    DISTINCT( 'Contract Rev2'[Review No] ),
    "@Lev5Tot", [Level5 Total]
)

VAR Result =
AVERAGEX(
    vTable,
    [@Lev5Tot]
)

RETURN
Result

vTable recreates the table below virtually within the measure and holds it in a variable for future use:

image

That future use then comes in the next variable, which takes the sum of the Level 5 measure from the virtual table. The exact same principles are applied in the SumX Card and CountX card measures (although DAX got the count correct, I like to be sure of what it’s doing so I included a CountX measure.

The power of evaluation context (and iteratring functions) can be seen when you drop the SumX card measure back into the visual containing the Review No context:

image

It evaluates each row in context, returning the proper value, and then when it gets to the total row (without any context), it evaluates over the entire virtual table - in effect treating that total row as it did the card measure.

As you move forward in DAX, these are critically important concepts to master. Fortunately, @sam.mckay has a ton of fantastic content on evaluation, filter and row context, virtual tables and fixing incorrect totals. Also, @Greg has put together an exceptional compendium of portal and forum resources on the incorrect totals problem in the DAX patterns section of the forum:

He points to a lot of the videos of Sam’s that I would recommend you watch, so that might be the best place to start.

I hope this is helpful. Revised solution file posted below (new stuff is on p. 2)

Brian,

Thanks you very much for your assistance, guidance and the reference material, you are a gem!

I obviously have lot’s to learn and understand, as I say if I can learn something new each day, retain what I’ve learned, and apply consistently, I call it continuous improvements!

Thanks
J

@jprlimey,

My pleasure - definitely a mutually beneficial process. The “tough nuts to crack” on the forum are great practice and the means by which I’ve learned the bulk of what I know after building the foundation from @sam.mckay’s videos.

To your point about learning/practice/continuous improvement, you may find the strategies in this video useful:

Enjoy the journey!

  • Brian