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

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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'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 )