Forum Discussion
Calculated column analysis
Hello everyone, I wante to post this in order to improve my skills and get a better understanding of Power BI.
The problem I solved is create a calculated column that concatenates other columns that have written one product name, so we have column 1 = product 1, column 2 = product 2... until 5 in my case. The tricky part is that I want to display the result in alphabetic order to be able to do some calculations based on the columns without having duplicated data because the name of the products are not ordered at the beginning.
So this is the code I created:
cadena productos ordenados =
var p1 = 'Table'[Product 1]
return
var p2 = 'Table'[Product 2]
return
var p3 = 'Table'[Product 3]
return
var p4 = 'Table'[Product 4]
return
var p5 = 'Table'[Product 5]
return
var word5 =
MAX(
MAX(
MAX(
MAX(
p1;
p2
);
p3
);
p4
);
p5
)
return
var word4 =
MAX(
MAX(
MAX(
MAX(
IF(p1=word5;BLANK();p1);
IF(p2=word5;BLANK();p2)
);
IF(p3=word5;BLANK();p3)
);
IF(p4=word5;BLANK();p4)
);
IF(p5=word5;BLANK();p5)
)
return
var word3 =
MAX(
MAX(
MAX(
MAX(
IF(OR(p1=word5;p1=word4);BLANK();p1);
IF(OR(p2=word5;p2=word4);BLANK();p2)
);
IF(OR(p3=word5;p3=word4);BLANK();p3)
);
IF(OR(p4=word5;p4=word4);BLANK();p4)
);
IF(OR(p5=word5;p5=word4);BLANK();p5)
)
return
var word2 =
MAX(
MAX(
MAX(
MAX(
IF(OR(p1=word3;OR(p1=word5;p1=word4));BLANK();p1);
IF(OR(p2=word3;OR(p2=word5;p2=word4));BLANK();p2)
);
IF(OR(p3=word3;OR(p3=word5;p3=word4));BLANK();p3)
);
IF(OR(p4=word3;OR(p4=word5;p4=word4));BLANK();p4)
);
IF(OR(p5=word3;OR(p5=word5;p5=word4));BLANK();p5)
)
return
var word1 =
MAX(
MAX(
MAX(
MAX(
IF(OR(p1=word2;OR(p1=word3;OR(p1=word5;p1=word4)));BLANK();p1);
IF(OR(p2=word2;OR(p2=word3;OR(p2=word5;p2=word4)));BLANK();p2)
);
IF(OR(p3=word2;OR(p3=word3;OR(p3=word5;p3=word4)));BLANK();p3)
);
IF(OR(p4=word2;OR(p4=word3;OR(p4=word5;p4=word4)));BLANK();p4)
);
IF(OR(p5=word2;OR(p5=word3;OR(p5=word5;p5=word4)));BLANK();p5)
)
return
CONCATENATE(
word1;
CONCATENATE(
IF(ISBLANK(word1);BLANK();", ");
CONCATENATE(
word2;
CONCATENATE(
IF(ISBLANK(word2);BLANK();", ");
CONCATENATE(
word3;
CONCATENATE(
IF(ISBLANK(word3);BLANK();", ");
CONCATENATE(
word4;
CONCATENATE(
IF(ISBLANK(word4);BLANK();", ");
word5
)
)
)
)
)
)
)
)
I think this code can be improved with maybe virtual tables but I don't know yet how to use them properly. I will be happy to receive any feedback from you.
Thank you!
10 Replies
Well, isThisABug here is a potential improvement. I'm not entirely happy with it because it is still tied to a reasonable maximum approach, you have to code it for the number of items in your sort. But, it is potentially a starting place. PBIX is attached.
Column = VAR __Table = { [Product 1], [Product 2], [Product 3], [Product 4], [Product 5] } VAR __word5 = MAXX(__Table,[Value]) VAR __word4 = MAXX(EXCEPT(__Table,{ __word5 }),[Value]) VAR __word3 = MAXX(EXCEPT(__Table,{ __word5, __word4 }),[Value]) VAR __word2 = MAXX(EXCEPT(__Table,{ __word5, __word4, __word3 }),[Value]) VAR __word1 = MINX(__Table,[Value]) RETURN CONCATENATEX({__word1, __word2, __word3, __word4, __word5 },[Value],",")I like your implementation of a bubble sort, I'm basically using the same technique.
Oh yeah, duh, forgot CONCATENATEX has an order by expression:
Sort 3 = VAR __Table = { [Product 1], [Product 2], [Product 3], [Product 4], [Product 5] } RETURN CONCATENATEX(__Table,[Value],",",[Value],ASC)Or, in a single line:
Sort 4 = CONCATENATEX({ [Product 1], [Product 2], [Product 3], [Product 4], [Product 5] },[Value],",",[Value],ASC)Anonymous I'm not sure why a single line of DAX code is a square peg. I mean, you could possibly do it in a single line of M code, but I don't see that being any better than a single line of DAX code.
Here is the M code:
Text.Combine( List.Sort({[Product 1], [Product 2], [Product 3], [Product 4], [Product 5]} ),"," )M requires 2 functions to do what DAX can do in 1 function. Just, just pointing that out... 🙂 I suppose one of them is a square peg...
