Create new columns from other columns (bring column titles in the values)

[cherrypbi]

Hello,

Is it possible to achieve below two yellow columns? Basically, you will choose highest selling product in the best_sell column, and next best selling in the next_best_sell. Not sure if it's possible to achieve this since I'm bringing in column titles in the values. Any advice is appreciated 

[Nathaniel_C · Accepted solution]

Hi @cherrypbi, 

Modified : To get the column names, duplicate the table in Power BI, unpivot the table, delete other columns, delete duplicates, add an index and bring this table to PBI. Create measures for each row, and use those measures in first measure.

Name0 = CALCULATE(MAX('Best (2)'[Attribute]),FILTER('Best (2)','Best (2)'[Index]=0))

,

Best = 
var _toast = [Toast]
var _milk = [Milk]
var _eggs = [Eggs]


var _best = SWITCH (TRUE(),
AND(_toast > _milk, _toast > _eggs), 'Best (2)'[Name0],
AND(_milk > _toast, _milk > _eggs),'Best (2)'[Name1])

return _best

This way you can have many columns, and it will not matter what the column header is.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
 

[Nathaniel_C · Accepted solution]

Hi @cherrypbi ,
Now that we have worked out that all your columns are from measures as your data comes from other tables, here are two measures that will work for you  to find the best and the next best.  I ended up using virtual tables to work around the issue of many more columns than in the original picture, and that you do not have access to Power Query.

Here is my pbix https://1drv.ms/u/s!AgCd7AyfqZtE4i-TGMUDznY2ZU50?e=PctqdC 

Best w measures by Virtual Table = 
var _toast =[Sum of Toast]  // Get values
var _milk = [Sum of Milk]
var _eggs = [Sum of Eggs]
var _tea = [Sum of Tea]

var _createTable = {(_toast,"Toast"),(_milk, "Milk"),(_eggs, "Eggs"), (_tea, "Tea")}  //create virtual table with the row values are turned into column

var _max = MAXX(_createTable,[Value1])  //Get the max value of all the inputs

var _name= MINX (Filter(_createTable, [Value1] =_max), [Value2]) // filter table to that value and return the name

return _name

Next Best w measures by Virtual Table = 
var _topValue = [Best w measures by Virtual Table] //Get the namw of the best product


var _toast = IF( _topValue = "Toast",-100,[Sum of Toast]) //we want to take that value that we got from Best measure out of the running so giveit -100
var _milk= IF( _topValue = "Milk",-100,[Sum of Milk])
var _eggs = IF( _topValue = "Eggs",-100,[Sum of Eggs])
var _tea = IF( _topValue = "Tea",-100,[Sum of Tea])



var _createTable = {(_toast,"Toast"),(_milk, "Milk"),(_eggs, "Eggs"), (_tea, "Tea")}  //create virtual table with the row values are turned into column

var _max = MAXX(_createTable,[Value1])  //Get the max value of all the inputs

var _name= MINX (Filter(_createTable, [Value1] =_max), [Value2]) // filter table to that value and return the name

return _name

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @cherrypbi ,
Now that we have worked out that all your columns are from measures as your data comes from other tables, here are two measures that will work for you  to find the best and the next best.  I ended up using virtual tables to work around the issue of many more columns than in the original picture, and that you do not have access to Power Query.

Here is my pbix https://1drv.ms/u/s!AgCd7AyfqZtE4i-TGMUDznY2ZU50?e=PctqdC 

Best w measures by Virtual Table = 
var _toast =[Sum of Toast]  // Get values
var _milk = [Sum of Milk]
var _eggs = [Sum of Eggs]
var _tea = [Sum of Tea]

var _createTable = {(_toast,"Toast"),(_milk, "Milk"),(_eggs, "Eggs"), (_tea, "Tea")}  //create virtual table with the row values are turned into column

var _max = MAXX(_createTable,[Value1])  //Get the max value of all the inputs

var _name= MINX (Filter(_createTable, [Value1] =_max), [Value2]) // filter table to that value and return the name

return _name

Next Best w measures by Virtual Table = 
var _topValue = [Best w measures by Virtual Table] //Get the namw of the best product


var _toast = IF( _topValue = "Toast",-100,[Sum of Toast]) //we want to take that value that we got from Best measure out of the running so giveit -100
var _milk= IF( _topValue = "Milk",-100,[Sum of Milk])
var _eggs = IF( _topValue = "Eggs",-100,[Sum of Eggs])
var _tea = IF( _topValue = "Tea",-100,[Sum of Tea])



var _createTable = {(_toast,"Toast"),(_milk, "Milk"),(_eggs, "Eggs"), (_tea, "Tea")}  //create virtual table with the row values are turned into column

var _max = MAXX(_createTable,[Value1])  //Get the max value of all the inputs

var _name= MINX (Filter(_createTable, [Value1] =_max), [Value2]) // filter table to that value and return the name

return _name

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[cherrypbi]

Question,, if there's no "Next best measure" (ex. merchant had revenue only on Milk and no other products, thus the Next best measure should return blank?).
Right now both "Best measure" and "Next best measure" is returning same values for that example.. is this something achievable in the same measure or need to create other logic? 

[Nathaniel_C · Accepted solution]

Hi @cherrypbi ,
Here is my solution for your request.  Just make a small change to the previous measure. I made the same change to both measures.  However you may want a different return on the first measure if the merchant did not sell anything.  Such as "No Sales" instead of "None." Obviously whatever you wish can be added.

Best w measures by Virtual Table =
VAR _toast = [Sum of Toast] // Get values
VAR _milk = [Sum of Milk]
VAR _eggs = [Sum of Eggs]
VAR _tea = [Sum of Tea]
VAR _createTable = { ( _toast, "Toast" ), ( _milk, "Milk" ), ( _eggs, "Eggs" ), ( _tea, "Tea" ) } //create virtual table with the row values are turned into column
VAR _max =
    MAXX ( _createTable, [Value1] ) //Get the max value of all the inputs
VAR _name =
    IF (                              //Added IF() function
        _max <= 0,
        "None",
        MINX ( FILTER ( _createTable, [Value1] = _max ), [Value2] )
    ) // filter table to that value and return the name
RETURN
    _name

Next Best w measures by Virtual Table =
VAR _topValue = [Best w measures by Virtual Table] //Get the namw of the best product
VAR _toast =
    IF ( _topValue = "Toast", -100, [Sum of Toast] ) //we want to take that value that we got from Best measure out of the running so giveit -100
VAR _milk =
    IF ( _topValue = "Milk", -100, [Sum of Milk] )
VAR _eggs =
    IF ( _topValue = "Eggs", -100, [Sum of Eggs] )
VAR _tea =
    IF ( _topValue = "Tea", -100, [Sum of Tea] )
VAR _createTable = { ( _toast, "Toast" ), ( _milk, "Milk" ), ( _eggs, "Eggs" ), ( _tea, "Tea" ) } //create virtual table with the row values are turned into column
VAR _max =
    MAXX ( _createTable, [Value1] ) //Get the max value of all the inputs
VAR _name =
    IF (               //Added IF() function
        _max <= 0,
        "None",
        MINX ( FILTER ( _createTable, [Value1] = _max ), [Value2] )
    ) // filter table to that value and return the name
RETURN
    _name

Here is my pbix. https://1drv.ms/u/s!AgCd7AyfqZtE4i-TGMUDznY2ZU50?e=utjcVt 

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[cherrypbi]

GREAT solution, thank you so much!! 

[v-jianboli-msft]

Hi @cherrypbi ,

Please try:

First Unpivot columns:

Then add columns:

Pivot the two columns:

Here is the M code:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WclTSUTI0MDAAUqZg0kApVidayQnOB0kixJ3BLLAoTGNsLAA=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Merchant = _t, Toast = _t, Milk = _t, Eggs = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Merchant", type text}, {"Toast", Int64.Type}, {"Milk", Int64.Type}, {"Eggs", Int64.Type}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type", {"Merchant"}, "Attribute", "Value"),
    #"Added Custom" = Table.AddColumn(#"Unpivoted Columns", "best_sell", each Table.Max(
Table.SelectRows(#"Unpivoted Columns",(x)=>x[Merchant]=[Merchant]),"Value")[Attribute]),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "nest_best_sell", each Table.Max(
Table.SelectRows(#"Unpivoted Columns",(x)=>x[Merchant]=[Merchant] and x[Attribute]<>[best_sell]),"Value")[Attribute]),
    #"Pivoted Column" = Table.Pivot(#"Added Custom1", List.Distinct(#"Added Custom1"[Attribute]), "Attribute", "Value"),
    #"Reordered Columns" = Table.ReorderColumns(#"Pivoted Column",{"Merchant", "Toast", "Milk", "Eggs", "best_sell", "nest_best_sell"})
in
    #"Reordered Columns"

Final output:

Best Regards,

Jianbo Li

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

[cherrypbi]

I'm unable to use this method as I'm using measures for the table values! but appreciate your help 

[Nathaniel_C]

Hi @cherrypbi ,

You might try this:

Best = 
var _toast = [Toast]
var _milk = [Milk]
var _eggs = [Eggs]

var _best = SWITCH (TRUE(),
AND(_toast > _milk, _toast > _eggs), "Toast",
AND(_milk > _toast, _milk > _eggs),"Milk")

return _best

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C · Accepted solution]

Hi @cherrypbi, 

Modified : To get the column names, duplicate the table in Power BI, unpivot the table, delete other columns, delete duplicates, add an index and bring this table to PBI. Create measures for each row, and use those measures in first measure.

Name0 = CALCULATE(MAX('Best (2)'[Attribute]),FILTER('Best (2)','Best (2)'[Index]=0))

,

Best = 
var _toast = [Toast]
var _milk = [Milk]
var _eggs = [Eggs]


var _best = SWITCH (TRUE(),
AND(_toast > _milk, _toast > _eggs), 'Best (2)'[Name0],
AND(_milk > _toast, _milk > _eggs),'Best (2)'[Name1])

return _best

This way you can have many columns, and it will not matter what the column header is.

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel
 

[cherrypbi]

This worked great!!! thanks so much! how would you implement this for "Next Best" column?

[Nathaniel_C]

Hi @cherrypbi ,
I did not have your whole table in my first example.  It is here, now. I added a third row as a measure, and input your actual numbers.

Name2 = CALCULATE(MAX('Best (2)'[Attribute]),FILTER('Best (2)','Best (2)'[Index]=2))

The logic is a little more complex for next best - the symbol "||" means "or" in DAX. We have to check whether the product is less than one of the other two products and greater than one of them.

NextBest = 
var _toast = [Toast]
var _milk = [Milk]
var _eggs = [Eggs]


var _nextBest = SWITCH (TRUE(),
AND(_toast < _milk, _toast > _eggs)|| AND(_toast > _milk, _toast <_eggs),'Best (2)'[Name0],
AND(_milk < _toast, _milk > _eggs) || AND(_milk > _toast, _milk < _eggs),'Best (2)'[Name1],
AND( _eggs< _toast, _eggs>_milk)|| AND( _eggs> _toast, _eggs<_milk),'Best (2)'[Name2])

return _nextBest

 Below is the result of the visual. You can turn on and off the Total as needed.  Please double check this to make sure that it fits your requirements.

Thank you!
Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[cherrypbi]

Thank you so much!! will try to implement the next best column.

• One question though, so my table has additional columns other than Toast, Milk, and Eggs column and has alot of data. When I add in the Best column, the memory maxes out and I can't display the table.. any advice on how to optimize this? it seems to be working when I only pull Merchant + Best columns, but whe I have other columns it can't be displayed

[Nathaniel_C]

Hi @cherrypbi ,
In Power Query - which is the right place to do this, we can add the column:

if [Toast] > [Milk] and [Toast] > [Eggs] then "Toast" else if [Milk] > [Toast] and [Milk] > [Eggs] then "Milk" else if [Eggs] > [Toast] and [Eggs] > [Milk] then "Eggs" else null

This will take up a lot less memory.

Same answer:

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[cherrypbi]

Ah I see, that's really helpful too! But I realized I can't access the power query since my table is consisted of columns from different tables. So can't really go in the transform data and use this feature but really appreciate showing this way too 

[Nathaniel_C]

Ok, lets look at a measure then.

[Nathaniel_C]

Hi @cherrypbi ,
Here are the measures - results are the same. Small change to the top part.  I am thinking of a different measure for the next best measure.

Best Measure = 
var _toast = MAX(Best[Toast])  //add max and the table-column name
var _milk = MAX(Best[Milk])
var _eggs = Max(Best[Eggs])


var _best = SWITCH (TRUE(),
AND(_toast > _milk, _toast > _eggs), 'Best (2)'[Name0],
AND(_milk > _toast, _milk > _eggs),'Best (2)'[Name1],
AND( _eggs> _toast, _eggs>_milk),'Best (2)'[Name2])
return _best

NextBest Measure = 
var _toast = MAX(Best[Toast])  //add max and the table-column name
var _milk = MAX(Best[Milk])
var _eggs = Max(Best[Eggs])



var _nextBest = SWITCH (TRUE(),
AND(_toast < _milk, _toast > _eggs)|| AND(_toast > _milk, _toast <_eggs),'Best (2)'[Name0],
AND(_milk < _toast, _milk > _eggs) || AND(_milk > _toast, _milk < _eggs),'Best (2)'[Name1],
AND( _eggs< _toast, _eggs>_milk)|| AND( _eggs> _toast, _eggs<_milk),'Best (2)'[Name2])
return _nextBest

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @cherrypbi ,

Ok this is a better Next Best measure.  Get the value from the Best measure and assign it a value of -100.  This will take it out of the running for Best, and essentially we just rerun the logic for the best value.  Make sense? Less typing too!

NB measure = 

var _topValue = [Best Measure]  // The Results from the Best Measure

var _toast = IF( _topValue = "Toast",-100,MAX(Best[Toast])) //we want to take that value that we got from Best measure out of the running so giveit -100
var _milk= IF( _topValue = "Milk",-100,MAX(Best[Milk]))
var _eggs = IF( _topValue = "Eggs",-100,MAX(Best[Eggs]))


var _nextBest = SWITCH (TRUE(),   //then we only have to go through one logic test for each the samw as the best measure
AND(_toast > _milk, _toast > _eggs), 'Best (2)'[Name0],
AND(_milk > _toast, _milk > _eggs),'Best (2)'[Name1],
AND( _eggs> _toast, _eggs>_milk),'Best (2)'[Name2])
                                                        //if you have four columns each row will have 3tests or n-1, and you will have 4 rows- 1 for each column

return _nextBest

Let me know if you have any questions.

If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos 👍are nice too.
Nathaniel

[Nathaniel_C]

Hi @cherrypbi ,
Can you access Power Query if I show you how to add the columns there?  I may also have run across another way to get the names of the columns.

Please let me know,

Nathaniel

[cherrypbi]

Hi Nathaniel, sure I should be able to .
I forgot to mention that those 'Milk', 'Eggs', 'Toast' are coming from some calculated measures from other tables. 

[Nathaniel_C]

Hi @cherrypbi ,
At work, but will get back to you today.
In the meantime, since it works would you mark it as a solution, so that others may find it more easily?

Thank you,

Nathaniel