Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hey guys,
I want to create a column where every value just appears one time.
DISTINCT will create a circular dependency.
CONCATENATEX(DISTINCT(FILTER('BaseData','BaseData'[OrderNumber]=EARLIER('BaseData'[OrderNumber]))),'BaseData'[Team]," ")
A sample file is here: sample.pbix
I want that every value in the "s_t column" to just appear one time.
For example in the first row: there should be just one "A" in the s_t column.
or in the row where the "UnderOrderNumber" is "4", the row in the s_t colum should be "B C D"
Thanks in advance for any help!
Solved! Go to Solution.
@Anonymous
CONCATENATEX has an ORDER BY option we can include to get what you are looking for. Should have thought of that to begin with, sorry.
s_t =
CONCATENATEX (
CALCULATETABLE (
VALUES ( BaseData[team] ),
ALLEXCEPT ( BaseData, BaseData[OrderNumber] )
),
BaseData[team],
" ",
BaseData[team]
)
Thanks to all 3 of you guys @jdbuchanan71 @Fowmy @TomMartens
@jdbuchanan71was maybe the closest to the solution that I want. The only thing that is not perfect yet is, that the order of the letter isn't the same/ causes new groups of values.
For example:
"B C D"
and
"C B D"
involving the same teams, but showing of in different groups because of the letter order.
But also thanks to @Fowmy @TomMartens maybe my request wasn't specific enough. Would like to give all of you guys a "solution" badge!
Maybe somebody of you guys have a hint to put the letters in the right order 🙂
@Anonymous
CONCATENATEX has an ORDER BY option we can include to get what you are looking for. Should have thought of that to begin with, sorry.
s_t =
CONCATENATEX (
CALCULATETABLE (
VALUES ( BaseData[team] ),
ALLEXCEPT ( BaseData, BaseData[OrderNumber] )
),
BaseData[team],
" ",
BaseData[team]
)
thank you @jdbuchanan71 that worked out perfectly. I really appreciate the help! Have a good week 🙂
Hey @Anonymous ,
I created a calculated column using this dax statement:
output =
var _input = 'Table'[input]
var lengthInput = LEN( _input)
var _t1 = GENERATESERIES( 1 , lengthInput , 1)
var _t2 =
DISTINCT(
FILTER(
SELECTCOLUMNS(
GENERATE(
_t1
, var _index = ''[Value]
var _t3 = ROW("x" , MID( _input , _index , 1))
return
_t3
)
, "x" , [x]
)
, [x] <> " "
)
)
return
CONCATENATEX(
_t2
, [x]
)
I created a table with a column called input data, applying the dax from above the table will look like this:
Hopefully, this provides some ideas that will help you to tackle this challenge.
Regards,
Tom
@Anonymous
Add this as a new column to your table.
s_t =
VAR _ORDER = [OrderNumber]
VAR _UNDERORDER = [UnderOrderNumber]
VAR _A =
CALCULATETABLE(
VALUES(BaseData[team]),
ALLEXCEPT(BaseData,BaseData[OrderNumber])
)
VAR _X =
COUNTROWS(
FILTER(
BaseData,
BaseData[OrderNumber] = _ORDER && BaseData[UnderOrderNumber] <= _UNDERORDER
)
)
VAR _TEAM =
CONCATENATEX( _A, BaseData[team], " ")
RETURN
IF( _X = 1 , _TEAM , BLANK() )
________________________
If my answer was helpful, please consider Accept it as the solution to help the other members find it
Click on the Thumbs-Up icon if you like this reply 🙂
⭕ Subscribe and learn Power BI from these videos
⚪ Website ⚪ LinkedIn ⚪ PBI User Group
@Anonymous
Give this a try.
s_t =
CONCATENATEX (
CALCULATETABLE (
VALUES ( BaseData[team] ),
ALLEXCEPT ( BaseData, BaseData[OrderNumber] )
),
BaseData[team],
" "
)
Hey @Anonymous ,
I just realized that my "answer" by no means provides what you are looking for, as I did not fully understand the question. For this reason, try the solutions provided by @jdbuchanan71 and @Fowmy ,
Please excuse the distraction.
Regards,
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
83 | |
70 | |
60 |
User | Count |
---|---|
150 | |
115 | |
104 | |
89 | |
65 |