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
tgardner
Helper I
Helper I

Aggregating Value in Concatenated Field Not Working

I have a dataset that looks effectively like this:

 

TCPeriodCOUNT
xc1P31
xc2P41
xc1P41
yc1P41
zc1P41

 

I am attempting to concatenate Col-T with an aggregation of COUNT (i.e. x1, x2, y1, z1).

 

As you can see below, whilst aggregation of the COUNT column and the appropriate measure produces the correct summed up values, when trying to aggregate T with the measure or the calculation, it refuses to work.

 

TPeriodCOUNTMeasure - SUM(Count)Column - T&SUM(Count)Column - T&Measure
xP311x5x1
xP422x5x1
yP411y5y1
zP411z5z1

 

Need to keep granularity in data table so slicers and filters work for C and Period on the front end.

 

Hoping you can assist!

9 REPLIES 9
v-stephen-msft
Community Support
Community Support

Hi @tgardner ,

 

Based on my test, the COUNT column is created by the measure.

COUNT = COUNT('Table'[T])

 

Then create a measure to combine the two columns together.

Col-T = MAX('Table'[T])&[COUNT]

 

The result is as follows, and the slicer filter will change the result.

7.png8.png

 

 

You can check more details from here.

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi Stephen

 

This is a great solution to have the count dynamically change, thanks so much. Curious why I can't use this in a Treemap visualisation (so x1, y2 etc show on the map with appropriate proportions) and if you know how this can be achieved?

Hi @tgardner ,


Sorry to disturb you...


But did I answer your question ? Please mark my reply as solution. Thank you very much.

 

Best Regards,
Stephen Tao

Hi Stephen,

 

Not a bother in the slightest. I think we are very near a solution - you've really helped.

 

I'm not trying to put the text into the values field. I'm hoping to use the count of that concatenated text in the values field and the concatenated text displayed in the tree map. Does that make sense?


Thanks!!

Hi @tgardner ,

 

Because the format of the Col-T measure is text, we can't put a text format measure into Values in the Treemap.

32.png

 

 

 

Best Regards,

Stephen Tao

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

amitchandak
Super User
Super User

@tgardner ,

New measure = max(Table[T])  & divide(sum(Table[COUNT]), count(Table[COUNT]))

 

New measure 2 =max(Table[T])  & calculate(sum(Table[COUNT]), allselected(Table))

Hi

 

measure = MAX('Table'[T])&CALCULATE(SUM('Table'[COUNT])) works in terms of displaying this in a table.

but this measure cannot be placed in a tree map. do you know how to put this in a treemap?
amitchandak
Super User
Super User

@tgardner , Not able to understand your output and what is not working

I would like to Concatenate Column  T and a sum/count of COUNT column, i.e. x2, y1

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.