Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
LPenatti
Helper II
Helper II

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

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
smpa01
Super User
Super User

@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

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

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.

 

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.

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

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.

 

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
Resident Rockstar
Resident Rockstar

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

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]

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

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.