Forum Discussion

LPenatti's avatar
LPenatti
Icon for Helper II rankHelper II
7 years ago
Solved

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

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    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.

     

10 Replies

  • sturlaws's avatar
    sturlaws
    Icon for Resident Rockstar rankResident Rockstar
    7 years ago

    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's avatar
      LPenatti
      Icon for Helper II rankHelper II
      7 years ago

      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's avatar
        sturlaws
        Icon for Resident Rockstar rankResident Rockstar
        7 years ago

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

  • smpa01's avatar
    smpa01
    Icon for Community Champion rankCommunity Champion
    7 years ago

    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's avatar
      LPenatti
      Icon for Helper II rankHelper II
      7 years ago

      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's avatar
        v-juanli-msft
        Icon for Community Support rankCommunity Support
        7 years ago

        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.