Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello.
I have a table with 3 columns (SessionID, UserID, UsedFeature) and I want to create a calculated column with a flag that is 0 if a user has never used the feature in all of his sessions, 1 if he has only sessions that used the feature and 2 if he has sessions that used the feature and sesssions that did not.
I attach a table to see what I mean. The column in the blue circle is the one I want to create.
Thanks.
Solved! Go to Solution.
Hi @Anonymous ,
Try this:
NewColumn = VAR Num_Yes_ = CALCULATE ( COUNT ( Table1[UsedFeature] ), ALLEXCEPT ( Table1, Table1[UserID] ), Table1[UsedFeature] = "Yes" ) VAR Num_No_ = CALCULATE ( COUNT ( Table1[UsedFeature] ), ALLEXCEPT ( Table1, Table1[UserID] ), Table1[UsedFeature] = "No" ) VAR Num_Total_ = Num_Yes_ + Num_No_ RETURN SWITCH ( Num_Total_, Num_No_, 0, Num_Yes_, 1, 2 )
@AlB Thank you it works perfectly. I should really start looking into using variables. They are very useful.
@Anonymous Thanks for your answer. I didn't try your solytion as AIB's answer worked for me, but it looks like it will work as well.
Hi @Anonymous
Interesting solution. I'm curious, why do you need the CALCULATETABLE in VAR a? Wouldn't you get exactly the same table without it? i.e.:
VAR a = FILTER ( ALL ( 'Session' ), 'Session'[UserID] = EARLIER ( 'Session'[UserID] ) )
in fact, the ALL() is superfluous since no filter context is present:
VAR a = FILTER ( 'Session', 'Session'[UserID] = EARLIER ( 'Session'[UserID] ) )
@Anonymous - You could do something like this:
Flag = VAR a = CALCULATETABLE( 'Session', FILTER ( ALL ( 'Session' ), 'Session'[UserID] = EARLIER ( 'Session'[UserID] ) ) ) VAR b = MAXX(a,[UsedFeature]) VAR c = MINX(a,[UsedFeature]) RETURN SWITCH ( b & c, "NoNo", 0, "YesYes", 1, 2 )
or this:
Flag = VAR a = CALCULATE ( MIN ( 'Session'[UsedFeature] ), FILTER ( ALL ( 'Session' ), 'Session'[UserID] = EARLIER ( 'Session'[UserID] ) ) ) VAR b = CALCULATE ( MAX ( 'Session'[UsedFeature] ), FILTER ( ALL ( 'Session' ), 'Session'[UserID] = EARLIER ( 'Session'[UserID] ) ) ) RETURN SWITCH ( a & b, "NoNo", 0, "YesYes", 1, 2 )
Cheers!
Nathan
Hi @Anonymous ,
Try this:
NewColumn = VAR Num_Yes_ = CALCULATE ( COUNT ( Table1[UsedFeature] ), ALLEXCEPT ( Table1, Table1[UserID] ), Table1[UsedFeature] = "Yes" ) VAR Num_No_ = CALCULATE ( COUNT ( Table1[UsedFeature] ), ALLEXCEPT ( Table1, Table1[UserID] ), Table1[UsedFeature] = "No" ) VAR Num_Total_ = Num_Yes_ + Num_No_ RETURN SWITCH ( Num_Total_, Num_No_, 0, Num_Yes_, 1, 2 )
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |