Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Finding the top value as a calculated column with multiple filters

Hello I've been scratching my head for a while and I could really use some help 🙂

 

I have 3 fields which create a table which looks like this

 

CustomerCountryCreated
Customer 1UK100
Customer 1N/A500
Customer 2UK200
Customer 2N/A300
Customer 2USA2000
Customer 3USA200

 

It's a pretty simple table.  

 

What I'm struggling with is I want to modify the "N/A" values (which are text, not NULLS etc) for each Customer to the country with the highest [Created] value that is not N/A

 

So in this table I want to change the 500 N/A values for Customer1 to UK (as UK is the highest).  And I want to change the 300 N/A values to USA for Customer2 (as USA has the highest).

 

I've tried and tried and tried but just can't get this working

 

Any help would be appreciated.

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    Anonymous , Ceate two new columns

     


    rank = rankx(filter(Table, [customer] = earlier([customer])),[Created],,desc,dense)

     

    New country =
    var _max = maxx(filter(Table, [customer] = earlier([customer]) && [Rank] =1), [Country])
    return
    if([Country] ="N/A", _max, [Country])

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      amitchandak 

      Thanks for the reply 🙂  I'm not sure why but when I try RankX against the customer name, including the code for the first column you suggested, I get the error - CUSTOMDATA, USERNAME, USERCULTURE and USERPRINCIPALNAME functions are not supported in calculated tables/columns.  These functions may only be used in Measures or in the AllowedRowsExpression.

       

      I can't seem to find why. 

      • Anonymous's avatar
        Anonymous
        Not applicable
        5 years ago

        hi amitchandak do you have any idea why that error would be appearing?  I don't seem to be able to use Rankx as a calculated field anywhere in this document even though I can in others.

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous,

    Do you mean to create a dynamic calculated column that can be affected by filter effects?
    If that is the case, current power bi does not support create dynamic calculated column/table based on filter/slicer. They are host on different data levels and you can't use child level to affect its parent. I'd like to suggest you use the measure instead.

    Notice: the data level of power bi.

    Database(external) -> query table(query, custom function, query parameters) -> data model table(table, calculate column/table) -> data view with virtual tables(measure, visual, filter, slicer)

    Regards,

    Xiaoxin Sheng