topic DAX help group by distinct count measure in DAX Commands and Tips

DAX help group by distinct count measure

Anonymous — Thu, 11 Jun 2020 19:50:00 GMT

Hello,

I am a recent user of PBI and i am struggling with some data.

I need help to write two measures:

- one measure that group by "bank" and count unique "process" with errors

- one measure that group by "bank" and count total number of unique process received

here is the table and the desired results:

Thank you in advance!

Re: DAX help group by distinct count measure

Anonymous — Thu, 11 Jun 2020 20:53:07 GMT

Hi Anonymous

DAX does a good job of using the context of what's in a table to group things for you. The DAX for what you're looking for is pretty simple, we just need to write a formula that counts the unique processes with errors, and then another similar formula for calculating just the unique processes.

Distinct Processes with Errors = CALCULATE( DISTINCTCOUNT(process number), NOT(ISBLANK(error type)) ) Distinct Processes = DISTINCTCOUNT(process number)

Re: DAX help group by distinct count measure

Anonymous — Fri, 12 Jun 2020 06:13:29 GMT

Hi Anonymous ,

Create 2 measures

Result1 =

var _a = FILTER(ALLEXCEPT('Table','Table'[Bank]), 'Table'[ErrorType] <> BLANK())

RETURN

CALCULATE(DISTINCTCOUNT('Table'[Process]),_a)

Result2 = CALCULATE(DISTINCTCOUNT('Table'[Process]),ALLEXCEPT('Table','Table'[Bank]))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Re: DAX help group by distinct count measure

Anonymous — Sat, 13 Jun 2020 16:27:25 GMT

Hello,

the code bellow didn't work 😞

NOT(ISBLANK(error type))

Re: DAX help group by distinct count measure

Anonymous — Sat, 13 Jun 2020 21:55:40 GMT

Hi,

I am also new to DAX. I was just wondering if we could use the following code for your 2nd requirement !

Table = SUMMARIZE(Sheet1,Sheet1[bank],"numb",DISTINCTCOUNT(Sheet1[process]))

Please let me know your inputs.

Thanks