Measure for table that splits out total on individual line

DAX DAX Calculations
1

Hi,

usually I’d try and post my attempt at my solution, but in this instance I’m kinda stumped.

I’ll use the image of my PBIX table with the London New York example which has 25 container failures which is summed using my measure Container Failures.

I’d love to create a measure where it will return the total no of failures which is 25 and then using Unichar 10 (I think) it will split to a new line and then break out/break down the no of containers by Failure Reason as per the image I created in Excel. It will return a new line for each group of Failure Reason. The number of failure reasons will be dynamic, I currently have 14 different types of failure reason but not sure if that number would expand or not.

If there were no failures I’d want a blank returned.

Hopefully I’ve made my problem clear.EDNA Multi LIne Return.pbix (30.3 KB)

image

image
image961×480 7.42 KB

image

2

Hi @DavieJoe,

Silly question, is a matrix visual acceptable?

image

1 Like
3

Hey Melissa, in a normal world yes, in this instance it needs to be contained in one row.

4

I’m unsure if it’s possible but I’ve seen magic on here and something similar from @AntrikshSharma with ranking but that was limited to Top 4 and Bottom 4 and deployed in a measure and not a row of a table.

5

Hi @DavieJoe,

Had to ask…
No worries, give this a go.

Container Failures TEXT = 
VAR vTable = SUMMARIZE( Orders, Orders[From], Orders[To], Failures[failure_reason] )
RETURN
IF( ISINSCOPE( Orders[From] ),
    COMBINEVALUES( 
        UNICHAR( 10 ),
        [Container Failures],
        CONCATENATEX( vTable,
            [failure_reason] & " - " & FORMAT( [Container Failures], "@"),
            UNICHAR( 10 )
        )
    )
)
2 Likes
6

EDNA Multi LIne Return.pbix (33.2 KB)

It worked fine until I put it into the live model I used when I realised that the data model wasn’t correct in the example I posted. The table with the Failure Reasons can have multiples of the Order ID so you can have one order ID that has two failure Reasons so the relationship should be Many to 1 and this broke your measure.

7

Hi @DavieJoe,

Couple of notes.

  • You have a bi-directional relationship, I would suggest turning that into a single direction.
  • You had Word wrap disabled for Values, you need to enable that if you want multi lines

Give this a go.

Container Failures TEXT = 
VAR vTable = SUMMARIZE( Failures, Orders[From], Orders[To], Failures[failure_reason] )
RETURN
IF( ISINSCOPE( Orders[From] ),
    COMBINEVALUES( 
        UNICHAR( 10 ),
        [Container Failures],
        CONCATENATEX( vTable,
            [failure_reason] & " - " & FORMAT( [Container Failures], "@"),
            UNICHAR( 10 )
        )
    ),  FORMAT( [Container Failures], "@" )
)

I hope this is helpful.

2 Likes
8

@DavieJoe Just to add to Melissa’s solution just turn on “Word Wrap” under Values section.

1 Like
9

@Melissa @AntrikshSharma Thank you, this works beautifully, will help with my learning & understanding also.

The reason why I turned word wrap off is to create the effect of a Tooltip on a table. When I’d previously tried to create a tool tip on a table it shows over every single field and was annoying. So, in my image, it displays part of my table which has a lot of columns in it and space is at a premium. Column M is the current column where we wanted the breakdown/tooltip. M1 is the new measure provided by Melissa, I also added some spaces after the number to push the next characters out of view. So by shrinking the size of the column, adding some spaces after the number, turning off Word Wrap, we only see the number…but when you hover over you get the breakdown like a tooltip. :star_struck:

Thanks so much for your help, never fail to be impressed by everyone here.

image

3 Likes