cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
LPenatti Frequent Visitor
Frequent Visitor

Order By using number in concatenateX

Hi everyone!

 

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

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

picture_2

picture_3

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Order By using number in concatenateX

Hi @LPenatti 

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

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

Capture14.JPG

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.

 

View solution in original post

10 REPLIES 10
sturlaws New Contributor
New Contributor

Re: Order By using number in concatenateX

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 Frequent Visitor
Frequent Visitor

Re: Order By using number in concatenateX

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 New Contributor
New Contributor

Re: Order By using number in concatenateX

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

LPenatti Frequent Visitor
Frequent Visitor

Re: Order By using number in concatenateX

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

smpa01 Senior Member
Senior Member

Re: Order By using number in concatenateX

@LPenattiit might work

 

Measure = 

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

 Sample data for the measure

Column1
Brazil
South
North
Brazil

 

Table

 

Column1Pos
North1
Brazil2
South3

T2

LPenatti Frequent Visitor
Frequent Visitor

Re: Order By using number in concatenateX

Hi @smpa01, thank you for your reply!

 

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

card north.PNG

 

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.

 

Community Support Team
Community Support Team

Re: Order By using number in concatenateX

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 Frequent Visitor
Frequent Visitor

Re: Order By using number in concatenateX

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).

Community Support Team
Community Support Team

Re: Order By using number in concatenateX

Hi @LPenatti 

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

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

Capture14.JPG

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.

 

View solution in original post

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Analytics in Azure virtual event

Analytics in Azure virtual event

Experience a limitless analytics service built to ingest, prep, manage, and serve data for immediate use in Power BI.

Users Online
Currently online: 247 members 2,455 guests
Please welcome our newest community members: