DAX: Count number of non-unique items that show up with a 0 AND a positive number.

[Anonymous]

I have approx. 12 million rows of transactions in a table called 'InvTx'.  Pertinent columns are:

[Qty]:  (amount of the transaction),

[IMF]:  (item),

[Station]:  (location,

[Tx Type]:  (Type of the transaction, ie. adjustment, distribution, receipt)

I have created 2 formulas that appear to work correctly:

Counts the # of Unique [IMF] that have a Positive Distribution (which are errors in my system, distributions should be negative)

Unique IMF PositiveD =
 CALCULATE(
  DISTINCTCOUNT('InvTX'[IMF]),
  'InvTX'[Tx Type] IN { "DISTR.(REGULAR)", "DISTR.(EMERGENCY)", "DISTR.(CALL-IN)" },
        'InvTX'[Qty] > 0
 )

Counts the # of Unique [IMF] that have a 0 Distribution (shows a stock out)

Unique IMF StockOut =
 CALCULATE(
  DISTINCTCOUNT('InvTX'[IMF]),
  'InvTX'[Tx Type] IN { "DISTR.(REGULAR)", "DISTR.(EMERGENCY)", "DISTR.(CALL-IN)" },
        'InvTX'[Qty] = 0
 )

Now the result from this point is accurate! (i think?) Here is a screenshot.

The last formula in the table you see is this:

UniqueIMF StockOut and PositiveD =
COUNTAX(
'InvTX' , AND( InvTX[Unique IMF PositiveD] >0, InvTX[Unique IMF StockOut] >0 )
)

GOAL:

I want a total count of [IMF] that have both a stock out and a Positive distribution. (Only IMF 40123 qualifies above)

I have analyzed the raw data and the numbers created by the last formula are completley bogus.  I have no idea what it is counting!

Again, i would expect the last formula to = 0, 0, 0, 1.

I am only showing one [Station] in the table but I want to be able to remove the [IMF] column and show all [Station]s with the total count of [IMF] for each column.

Thank You in advance for any clarification!

[Anonymous]

To oversimplify...

Imagine a database with dates, people's names, school, and their favorite color.  Each month, the person picks a favorite color, which may change over time...

I want a formula that will tell me when the same person picked both blue and red in any given time period.  Ultimately, I want to be able to count the people in each school that have both choosen red and blue.

Result:

School 1 :  10 people (liked Red and Blue at different times throughout the year)

School 2:   5

School 3:   3

Any help?  I've tried all sorts of stuff. I fear I'm making this harder that it needs to be.

[AlbertoFerrari]

HI,

I like so much more your second question... speaking about people and colors make the approach much easier.

Now, the code is not too hard to write and it is super-fun. May I ask you to prepare a sample file with the relevant data (colors and poeple, please!) but not too trivial, so I can play with it a bit?

I know it looks weird, but my life is so much easier if I have data prepared, that I answer much more frequently to posts with raw data, the question and expected result. I'll happily do the rest of the work, that is helping you with DAX.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[Anonymous]

Thank You @AlbertoFerrari

I have created the sample file and have emailed it to you using your website. ( I have no means of uploading data where I work )

Feel free to post the sample file I sent for others to see if that is something you can do.

[AlbertoFerrari · Accepted solution]

Here you are. This code is more educational than performant, but it looks fine:

Question Red and Blue =
CALCULATE (
    COUNTROWS ( VALUES ( Test[Name] ) ),
    CALCULATETABLE (
        VALUES ( Test[Name] ),
        Test[Data Gathering Method] = "Questioned",
        Test[Color] = "Blue"
    ),
    CALCULATETABLE (
        VALUES ( Test[Name] ),
        Test[Data Gathering Method] = "Questioned",
        Test[Color] = "Red"
    )
)

Basically, the two CALCULATETABLE find the different names, CALCULATE then intersects it. You can also optimize it or use set functions (like UNION, INTERSECT, EXCEPT) and, on small datasets, it should work fine.

The file, for anybody interested in it, is here: https://www.dropbox.com/s/nq0bksn9uw5buwo/Red%20or%20Blue.pbix?dl=0

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com

[Anonymous]

Thank You so much!  I was not familiar with those functions.

[Anonymous]

@AlbertoFerrari.  You elluded that there might be a more robust solution?  I have approx. 12 million rows and the calculation works fine but it does hang when switching filters.  If you have the free time, I'd be interested in anything else you (or anyone else) can provide.

Thank you for posting the file as well.

[AlbertoFerrari]

Well, it should not be slow on 12M rows... but it depends also on the number of people, colors and other cardinalities involved. I understood that your scenario is more complex than just people and colors, so I am afraid any optimization on a demo data model will not work straight.

If you can share privately the model, I can take a look at it, just to tell you how complex it would be to optimize it. Not that I want to remain obscure in my explanation but, really, it depends on a ton of variables and a forum is not the right place where to talk about it, I would be very imprecise in any explanation.

Have fun with DAX!

Alberto Ferrari
http://www.sqlbi.com