Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

In table complex comparison to derive new column

Have the following table data

 

usageDateuserNameproductNameusageHourstokensConsumed
1/1/2020joea54
1/2/2020johnb225
1/3/2020joea24
1/1/2020johnb225
1/2/2020johna224
1/2/2020johnb45

 

I'm trying to add a column with the following criteria, but can't figure out how to do it... 

 

Value is:

- The number of tokens consumed, in the previous day, by the same user id (if exists), in the same product (if exists), if previous day hours > 20 (if exists)

 

So the desired result would be something like this (notes column added to explain value, don't expect this column)

 

usageDateuserNameproductNameusageHourstokensConsumedpreviousTokensnotes
1/1/2020joea540no usage at all by joe on 12/31/19
1/2/2020johnb555john used product b for >20 hrs on 1/1/20
1/3/2020joea240no usage at all by joe on 1/2/20
1/1/2020johnb2250no usage at all by john on 12/31/19
1/2/2020johna2240john did not use product a on 1/1/20
1/3/2020johnb450john used product b on 1/2 but not for more than >20 hrs

 

is this kind of inline comparison possible? or maybe i have to create some temporary tables to use?

Need Help
  • v-yingjl's avatar
    v-yingjl
    6 years ago

    Hi Anonymous ,

    You can create a measure to get the expected result:

    Previous Token = 
VAR _currentdate =
    SELECTEDVALUE ( 'Table'[usageDate] )
VAR _currentname =
    SELECTEDVALUE ( 'Table'[userName] )
VAR _currentproduct =
    SELECTEDVALUE ( 'Table'[productName] )
VAR _previoushour =
    CALCULATE (
        MAX ( 'Table'[usageHours] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [usageDate] = _currentdate - 1
                && [userName] = _currentname
                && [productName] = _currentproduct
        )
    )
VAR _previoustoken =
    CALCULATE (
        MAX ( 'Table'[tokensConsumed] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [usageDate] = _currentdate - 1
                && [userName] = _currentname
                && [productName] = _currentproduct
        )
    )
RETURN
    IF ( _previoushour > 20, _previoustoken, 0 )

    Here is the sample file hopes to help you, please try it: PBIX 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • v-yingjl's avatar
    v-yingjl
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi Anonymous ,

    You can create a measure to get the expected result:

    Previous Token = 
VAR _currentdate =
    SELECTEDVALUE ( 'Table'[usageDate] )
VAR _currentname =
    SELECTEDVALUE ( 'Table'[userName] )
VAR _currentproduct =
    SELECTEDVALUE ( 'Table'[productName] )
VAR _previoushour =
    CALCULATE (
        MAX ( 'Table'[usageHours] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [usageDate] = _currentdate - 1
                && [userName] = _currentname
                && [productName] = _currentproduct
        )
    )
VAR _previoustoken =
    CALCULATE (
        MAX ( 'Table'[tokensConsumed] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            [usageDate] = _currentdate - 1
                && [userName] = _currentname
                && [productName] = _currentproduct
        )
    )
RETURN
    IF ( _previoushour > 20, _previoustoken, 0 )

    Here is the sample file hopes to help you, please try it: PBIX 

     

    Best Regards,
    Yingjie Li

    If this post helps then please consider Accept it as the solution to help the other members find it more quickly.