Using Sumx() and hasonevalue() together as embedded sumif statement

[slider821]

Hi, I have a huge table in powerpivot, an excerpt of the table is below. I could do this in excel but don't know DAX well enough to figure this out.

I want to create MeasureX that looks for Code d1201 for each unique PatID and each unique Date, if it finds d1201 Code for that date and PatID, it will sum production for all codes for that Date and PatID.

Date	PatID	Code	Production
1/1/2014	X15205	d1201	30
1/1/2014	X15205	d151	10
1/1/2014	X15205	d1069	20
1/1/2014	Z1502	d1923	30
1/1/2014	V2312	d1283	40
1/2/2014	X15205	d123	10
1/3/2014	R1231	d3422	30
1/3/2014	R1231	d1923	80
1/3/2014	R1231	d1201	40

<tbody>
</tbody>

I am a DAX novice so the best I could come up with was

MeasureX:=CALCULATE([Production],Table

="d1201") 

which, as expected, only returns the Production of 30 for date 1/1/2014 and PatID X15205.


In this example, I would want MeasureX to return Production of 60 for date 1/1/2014 and PatID X15205.

Thanks in advance.

 

[scottsen]

Nice question! Let's build this up.

What can I say, I like simple base measures.
[Total Production] := SUM(Table[Production])

We know we want a total across ALL Codes...
[ALL Codes Production] := CALCULATE([Total Production], ALL(Table

))[/FONT]

[COLOR="#696969"]But secretly, I only want those that include at least 1 row of d1201.  Let's count those rows...[/COLOR]
[FONT=Courier New][Count d1201] := CALCULATE(COUNTROWS(Table), Table[Code] = "d1201")[/FONT]

[COLOR="#696969"]And finally, let's write our final measure, with filtering n such...[/COLOR]
[FONT=Courier New]MeasureX:=CALCULATE([All Codes Production], FILTER(VALUES(Table[PatID]), [Count d1201] > 0))[/FONT]

Which is to say "on a unique PatId basis... give me back rows that have at least 1 d1201, and calculate the [All codes production]".

I wish I had something more intelligent to say about the VALUES() call.  It's a whacky little function.  It generally services to return a table of the UNIQUE values of a column, but there are so many weird little uses of it (like this), that I really should blog about it.

Anyway, hope that gives you both a working model, and a framework for future work. :)

 

[slider821]

scottsen, thank you very much for your help. I really appreciate it.

I started with your formula:
MeasureX:=CALCULATE([All Codes Production], FILTER(VALUES(Table[PatID]), [Count d1201] > 0))

It looked through unique PatIDs but did not look through unique dates as well. I added to it:
MeasureX:=CALCULATE([All Codes Production], FILTER(VALUES(Table[PatID]), [Count d1201] > 0), FILTER(VALUES(Table[Date]), [Count d1201] > 0))

This got me closer but it's still not working properly. I'm not sure why my change fixed some of the issue but not the entire issue.

For the following table, MeasureX returns $589. It should return $316 and exclude the $273 from PatID 47020 on 3/21/14 (the red cells).

The end goal is for MeasureX to look for code D1201 within unique dates AND unique PatIDs, if it finds d1201, it returns all Production for all codes on that date AND PatID. PatID 47020 on 3/21/14 does not have code d1201 so it should not sum any of the production for 47020 on 3/21/14 ($273)

Date	PatID	Code	Production
2/21/2014	47020	D1201	33
2/21/2014	47020	D1206	24
2/21/2014	47020	D1120	43
2/21/2014	47020	D1101	0
3/21/2014	47020	D1351	33
3/21/2014	47020	D2391	97
3/21/2014	47020	D7140	111
3/21/2014	47020	D9230	32
3/21/2014	47037	D1001	0
3/21/2014	47037	D1201	36
3/21/2014	47037	D0220	20
3/21/2014	47038	D1201	36
3/21/2014	47038	D1206	57
3/21/2014	47071	D1001	0
3/21/2014	47071	D1201	33
3/21/2014	47071	D0220	22
3/21/2014	47071	D0230	12

<tbody>
</tbody>

Did I add the date formula incorrectly or do we need to add something else. I was thinking maybe an AND() operator with both FILTER formulas inside, but that errored out on me. Thanks again for your help

 

[scottsen]

Mostly because I have never actually used GENERATE() before, you have to tell me if this changes anything...

=CALCULATE([All Codes Production], FILTER(GENERATE(VALUES(Table1[PatID]), VALUES(Table1[Date])), [Count d1201] > 0))

 

[slider821]

that worked. scottsen you're a god amongst men. thank you

What exactly are Filter and Generate functions doing in this formula and how is it different from what we originally had?

 

[scottsen]

How do you feel about "I don't know"?

Well, FILTER() is pretty straight forward. It takes a table, applies some condition to each row and only rows that match the condition are returned.

=COUNTROWS(FILTER(Calendar, Calendar[Year] > 2001))

Is going to return a count of rows in the Calendar... that are 2002+.

Generate takes 2 tables and "mulitplies them together". A table of just months, and a table of just years, ... would return a table of all Month+Year combinations. So, my hope was that in your case, it would have all PatId+Date combos, then we could filter those down to just those returning some d1201 rows.

There is totally a more sane way to do this, I am sure.

I don't have time to try it today, but I suspect this would work and be way more... "normal":

[DaCount] := CALCULATE(COUNTROWS(Table1), ALLEXCEPT(Table1, Table1[Date], Table1[PadID]))
[DaAnswer] := CALCULATE([Total Production], FILTER(Table1, [DaCount] > 0)