Query Regarding DAX

[KunalKochar]

Hello All,

I am new to Power BI and stuck at a problem:

Attached is an screenshot of an example sample data. I need to find out the count of users who 'only' performed event 'B'
So, as per the example given, the output should be one user, because user 3 performed only event 'B'.
Any idea how can I write DAX query for this problem?

[v-eqin-msft · Accepted solution]

Hi @KunalKochar ,

You could create a flag measure:

Flag = 
var _dis=CALCULATE(DISTINCTCOUNT('Table'[Event]),ALLEXCEPT('Table','Table'[User]))
var _countB=CALCULATE(COUNTROWS('Table'),FILTER('Table',[User]=MAX('Table'[User]) && [Event]="B"))
RETURN IF(_dis=1 && _countB>0,1,0)

And then apply it to visual-level filter pane, set as "=1":

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[v-eqin-msft · Accepted solution]

Hi @KunalKochar ,

Did my method help you ? Please feel free to ask.

Best Regards,
Eyelyn Qin

[v-eqin-msft · Accepted solution]

Hi @KunalKochar ,

You could create a flag measure:

Flag = 
var _dis=CALCULATE(DISTINCTCOUNT('Table'[Event]),ALLEXCEPT('Table','Table'[User]))
var _countB=CALCULATE(COUNTROWS('Table'),FILTER('Table',[User]=MAX('Table'[User]) && [Event]="B"))
RETURN IF(_dis=1 && _countB>0,1,0)

And then apply it to visual-level filter pane, set as "=1":

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KunalKochar]

Hi @tackytechtom 
Thanks for your reply!

But, applying your solution gives an answer of 3, because there are three users in total who performed event = B.
I need users who performed 'only' event 'B', in this case only user '3'

[tackytechtom]

Hi @KunalKochar ,

My bad! I did not read your requirement properly. 

How about this one here:

Measure = 
VAR _helpTable = 
SUMMARIZE (
    Table,
    Table[User],
    "CountEventB", COUNTROWS ( FILTER ( Table, Table[Event] = "B") ),
    "CountOtherEvents", COUNTROWS ( FILTER ( Table, Table[Event] <> "B") )
)
RETURN
COUNTROWS ( FILTER ( _helpTable, [CountEventB] >= 1 && [CountOtherEvents] = 0 ) )

The idea is to create a _helpTable variable which would return the following result:

From there it is just choosing the one that has value >= 1 in the first column and a value that equals 0 (or BLANK) in the second column.

Let me know if this helps or if you have any questions 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/

[KunalKochar]

Dear @tackytechtom 
Thanks for the answer, I applied the same concept as this and it worked. Please find the DAX below. Although I have two follow-up questions on this, hoping you could answer this as well,
1. What if I have the user column in different table and not in 'Data' table I tried, VAR B_Users = CALCULATETABLE ( DISTINCT ( DifferentTable[User] ), Data[Event] = "B" ) But this is giving me blank output.
2. To extend my original question, what if I want to calculate list of users who performed only event B or C or more than two events? Do I have to manually write the above DAX for each event and then count distinct user id

VAR B_Users = CALCULATETABLE ( DISTINCT ( Data[User] ), Data[Event] = "B" )

VAR Non_B_Users = CALCULATETABLE ( DISTINCT ( Data[User] ), Data[Event] <> "B" )

VAR B_Only_Users = EXCEPT ( B_Users, Non_B_Users )

RETURN COUNTROWS ( B_Only_Users )

[wdx223_Daniel]

NewTable=FILTER(VALUES(table[User]),CALCULATE(DISTINCTCOUNT(table[Event]))=1)

[tackytechtom]

Hi @KunalKochar ,

How about this:

Measure = COUNTROWS ( FILTER ( Table, Table[Event] = "B") )

Let mw know if this helps 🙂

/Tom
https://www.tackytech.blog/
https://www.instagram.com/tackytechtom/