Order By using number in concatenateX

[LPenatti]

Hi everyone!

I have a chiclet slicer and a card visulization. The card shows what's selected on chiclet slicer (picture 1)

To do it I used the following function as a measure:

Region_selection = 

VAR iCount = COUNTROWS(VALUES(Database[Region]))
VAR Region = CONCATENATEX(VALUES(Database[Region]); [Region]; ", ")

RETURN Region

The table for chiclet slicer and relationship are shown below

In my table the order is North, Brazil and South.

I need the same order in the card when regions are selected, but I cannot order by the column "Pos" in concatenateX.

Is it possible to order it the way I need?

Thank you

[smpa01]

@LPenattiit might work

Measure = 

CONCATENATEX('Table','Table'[Column1],",",RELATED(T2[Pos]))

 Sample data for the measure

Column1

Brazil

South

North

Brazil

Table

Column1	Pos
North	1
Brazil	2
South	3

T2

[LPenatti]

Hi @smpa01, thank you for your reply!

Let's say it almost worked, but now it's showing repeated values:

I said it "almost worked" because it started with North, exactly the way I want.

The measure:

region_select = 

//old
//VAR _Concat = CONCATENATEX(VALUES('Database'[Region]); 'Database'[Region]; ", ")

//new
VAR _Concat = CONCATENATEX('Database'; 'Database'[Region]; ", "; RELATED('Latin America Map'[Pos]))

RETURN _Concat

I tried to substitute the first argument on CONCATENATEX 'Database' by VALUES('Database'[Region]) but it doesn't work.

[v-juanli-msft]

Hi @LPenatti 

What final result do you want?

If select map visual "north", the card visul should show "north",

If select map visual "south", the card visul should show "north, brazil,south", right?

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LPenatti]

Hi @v-juanli-msft , thank you for your reply.

The result I need is the Card visual shows what's selected in Map Visual filter, in the following order: "North, Brazil, South" (if all regions are selected, of course).

[v-juanli-msft · Accepted solution]

Hi @LPenatti 

Add "Name" column from "latin map" table  in the slicer, create a measure

Measure = CONCATENATEX(ALLSELECTED('Table'),'Table'[Name],",",'Table'[Pos])

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[LPenatti]

Hi @v-juanli-msft, thank you for your response.

It worked like a charm!!! TYVM!

I just added a Calculate function to get only countries with region <> "" because there are some countries with this field in blank in the system...

[sturlaws]

Hi @LPenatti,

I am not able to recreate this. Have you made sure that 'Latin America Map'[Name] is sorted by 'Latin America Map'[Pos]?

cheers,

S

[LPenatti]

Hi @sturlaws, thanks for your reply.

I forgot to mention it, but yes, 'Latin America Map'[Name] was already sort by 'Latin America Map'[Pos]

[sturlaws]

and you used the Sort by column-function to do this?

[LPenatti]

Hi @sturlaws, yes I've sorted by this function.