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

new column of array of values for each group but as a window rather than grouping function- DAX

Hi all. I have a table like this :

Hippocampus_1-1670158231882.png

 

What i want is  to create a new column in DAX that displayes an array if distinct colours for  each ID. So what i want is not collapse the dataframe by grouping by the IDs, but instead one new column like the image below.

 

Hippocampus_2-1670158378573.png

Thank you all!

1 ACCEPTED SOLUTION

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1670249457348.png

 

 

 

ID / Column = 
CONCATENATEX (
    DISTINCT ( colours[   ID] ),
    colours[   ID] & ":"
        & CONCATENATEX (
            SUMMARIZE (
                FILTER ( colours, colours[   ID] = EARLIER ( colours[   ID] ) ),
                colours[Colour]
            ),
            colours[Colour],
            ", "
        ),
    UNICHAR ( 10 )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


View solution in original post

6 REPLIES 6
Hippocampus
Frequent Visitor

@Jihwan_Kim Thank you very much, that's what i wanted! I know I'm asking for too much but what changes should I make to this function if i would like it as a measure instead (I want it to change dynamically upon filtering the table, that's why i would like it also as a measure instead of  column in this case).

Hi,

Thank you for your message.

I don't know how your datamodel looks like, or how your expected outcome of the visualization looks like.

Please share your sample pbix file's link with how the expected outcome of the visualization looks like.

Thanks.

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


@Jihwan_Kim Thanks for the response. Below is a link with the pbix file. I cannot upload it directly because I am a new member.

https://www.dropbox.com/s/jx9kklf0egq4v4g/colours.pbix?dl=0

 

 

Hippocampus_0-1670241907690.png

So as you can see above, I have a dynamic title on the top left which changes based on the filter measure based on the slicer. I have managed to create an array of all colours which is not what i want. What i want is on the image below(bassically display the selected ids, with their respective colours like below. Thank you!

 

Hippocampus_1-1670242032317.png

 

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1670249457348.png

 

 

 

ID / Column = 
CONCATENATEX (
    DISTINCT ( colours[   ID] ),
    colours[   ID] & ":"
        & CONCATENATEX (
            SUMMARIZE (
                FILTER ( colours, colours[   ID] = EARLIER ( colours[   ID] ) ),
                colours[Colour]
            ),
            colours[Colour],
            ", "
        ),
    UNICHAR ( 10 )
)

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


Amazing, thank you!

Jihwan_Kim
Super User
Super User

Hi,

Please check the below picture and the attached pbix file.

 

Jihwan_Kim_0-1670159165775.png

 

 

Colour Array CC =
CONCATENATEX (
    SUMMARIZE (
        FILTER ( 'Table', 'Table'[ID] = EARLIER ( 'Table'[ID] ) ),
        'Table'[Colour]
    ),
    'Table'[Colour],
    ", ",
    'Table'[Colour]
)

 

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Go to My LinkedIn Page


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.