Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

Create a calculated column based on another value in the same table

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.

 

calculated column.PNG

 

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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 )

 

 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@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
Not applicable

@AlB - You're absolutely correct - thank you for catching that!

Anonymous
Not applicable

@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

AlB
Super User
Super User

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 )

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.