Forum Discussion
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 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?
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.
7 Replies
- tackytechtom
Most Valuable Professional
Hi Anonymous ,
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/ - wdx223_Daniel
Community Champion
NewTable=FILTER(VALUES(table[User]),CALCULATE(DISTINCTCOUNT(table[Event]))=1)
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 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/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 idVAR 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 )
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.
