locked
PowerPivot Excel 2013 Calculted Fields using Usersname RRS feed

  • Question

  • Hello,

    I'm using Excel 2013 with PowerPivot.  I have one
    table containing a column with the sales person (verkoper) please see the
    screenshot for more details.  I'm looking for a way to add a calculated
    column, the value should depend on the user that is logged in.<o:p></o:p>

    In Tsql it's pretty easy using something like “case
    when verkoper = current_user then verkoper else 'other' end”<o:p></o:p>

    Is there a way to do this in Excel PowerPivot. Help
    would be greatly appreciated, I’ve been looking for an answer for some time
    now. I guess I’m looking at the wrong direction….??

    Sorry.. not able to insert the picture yet


    Tuesday, April 1, 2014 6:49 AM

Answers

  • Hello Wouter,

    In DAX there is a function USERNAME() available to get the current user Name, but you can't use this function in a calculated column Expression, only in measures and "AllowedRowsExpressions".


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Charlie Liao Thursday, April 3, 2014 8:39 AM
    • Marked as answer by Michael Amadi Friday, April 11, 2014 8:38 AM
    Tuesday, April 1, 2014 8:10 AM
  • Hey Wouter,

    as the Power Pivot Model is static to the point that you have to refresh the data to refresh the calculated columns I think this is not what you want to achieve.

    Imagine I (User A) create a PowerPivot, create a calculated column which represents what you want to see. I then share this file with you (User B). As soon as you open the PowerPivot Model you will see "my" results. You would have to refresh the data to achieve the result depending on User B.

    Even if it would work I doubt it would meet your expectation.

    As Olaf just said, the only way would be to work it as a measure.

    I hope I was of some help!

    ___________________________________

    Kind regards, please mark helpful posts! :)

    • Proposed as answer by Charlie Liao Thursday, April 3, 2014 8:39 AM
    • Marked as answer by Michael Amadi Friday, April 11, 2014 8:38 AM
    Tuesday, April 1, 2014 12:39 PM

All replies

  • Hello Wouter,

    In DAX there is a function USERNAME() available to get the current user Name, but you can't use this function in a calculated column Expression, only in measures and "AllowedRowsExpressions".


    Olaf Helper

    [ Blog] [ Xing] [ MVP]

    • Proposed as answer by Charlie Liao Thursday, April 3, 2014 8:39 AM
    • Marked as answer by Michael Amadi Friday, April 11, 2014 8:38 AM
    Tuesday, April 1, 2014 8:10 AM
  • Hey Wouter,

    as the Power Pivot Model is static to the point that you have to refresh the data to refresh the calculated columns I think this is not what you want to achieve.

    Imagine I (User A) create a PowerPivot, create a calculated column which represents what you want to see. I then share this file with you (User B). As soon as you open the PowerPivot Model you will see "my" results. You would have to refresh the data to achieve the result depending on User B.

    Even if it would work I doubt it would meet your expectation.

    As Olaf just said, the only way would be to work it as a measure.

    I hope I was of some help!

    ___________________________________

    Kind regards, please mark helpful posts! :)

    • Proposed as answer by Charlie Liao Thursday, April 3, 2014 8:39 AM
    • Marked as answer by Michael Amadi Friday, April 11, 2014 8:38 AM
    Tuesday, April 1, 2014 12:39 PM