DAX help group by distinct count measure

[Anonymous]

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!

[Anonymous]

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

[harshnathani · Accepted solution]

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)

[Anonymous]

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)

[Anonymous]

Hello,

the code bellow didn't work 😞

NOT(ISBLANK(error type))