How to show value just one time with "DISTINCT"?

[Anonymous]

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!

[Anonymous]

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 🙂 

[jdbuchanan71 · Accepted 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]
)

[Anonymous]

thank you @jdbuchanan71 that worked out perfectly. I really appreciate the help! Have a good week 🙂 

[TomMartens]

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

[Fowmy]

@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 🙂

YouTube  LinkedIn

[jdbuchanan71]

@Anonymous 

Give this a try.

s_t =
CONCATENATEX (
    CALCULATETABLE (
        VALUES ( BaseData[team] ),
        ALLEXCEPT ( BaseData, BaseData[OrderNumber] )
    ),
    BaseData[team],
    " "
)

[TomMartens]

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