Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Query Regarding DAX

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 exa...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

    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.

     

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Anonymous ,

     

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

     

    Best Regards,
    Eyelyn Qin

tackytechtom's avatar
tackytechtom
Icon for Most Valuable Professional rankMost Valuable Professional
4 years ago

Hi Anonymous ,

 

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/

 

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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 )