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

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
dobregon
Impactful Individual
Impactful Individual

Concatenatetext measure, not custom column with grand total in blank

Hi,

 

I'm trying to do create a measure that concatenate all the unique row values in a column but with a blank in the total, not in the subtotals. example

 

GROUPITEMDATECOUNTRY
PRIORITY11/1/2019SPAIN
PRIORITY12/1/2019SPAIN
PRIORITY13/1/2019SPAIN
PRIORITY14/1/2019SPAIN
PRIORITY15/1/2019SPAIN
PRIORITY21/1/2019FRANCE
PRIORITY22/1/2019FRANCE
PRIORITY23/1/2019FRANCE
PRIORITY24/1/2019FRANCE
PRIORITY25/1/2019FRANCE
NOT PRIORITY31/1/2019EEUU
NOT PRIORITY32/1/2019EEUU
NOT PRIORITY33/1/2019EEUU
NOT PRIORITY34/1/2019EEUU
NOT PRIORITY35/1/2019EEUU
NOT PRIORITY41/1/2019GERMANY
NOT PRIORITY42/1/2019GERMANY
NOT PRIORITY43/1/2019GERMANY
NOT PRIORITY44/1/2019GERMANY
NOT PRIORITY45/1/2019GERMANY

 

And I want to concatenate the unique values per item and show the values per item, per group and in the total i want to see a blank. I have done the following measure

Countries = 
CONCATENATEX (
    SUMMARIZE (
        FILTER ( Metadata, Metadata[Country] <> BLANK () ),
        Metadata[Country]
    ),
    Metadata[Country],
    ","
)

and the problem is that the result is

GROUPITEMCOUNTRY
NOT PRIORITY3EEUU
NOT PRIORITY4GERMANY
NOT PRIORITY Total EEUU, GERMANY
PRIORITY1SPAIN
PRIORITY2FRANCE
PRIORITY Total SPAIN, FRANCE
Grand Total EEUU, GERMANY,SPAIN, FRANCE

 

when in reiality for the big total i want to see a blank

GROUPITEMCOUNTRY
NOT PRIORITY3EEUU
NOT PRIORITY4GERMANY
NOT PRIORITY Total EEUU, GERMANY
PRIORITY1SPAIN
PRIORITY2FRANCE
PRIORITY Total SPAIN, FRANCE
Grand Total  

it could be possible?

Kind regards!!!



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)
1 ACCEPTED SOLUTION

@adityavighne  yes, is what i'm looking for. but the group could change (i will have 15 differnets columns of groups)

 

@dax yes i kno that this solve the problem for only one group, but in reality i have 15 columns for group this (differnet groups) so, with that i will need to calculate 15 measures depending of the group that i will to use in each visual. this is not a good thing.

I have solved the problem doing something strange. I have created a measure that count the disctinc countries in the column so, en each result of the matrixtable i will have the num of countries. Then i have created another measure that calculate the max distinctcount (so in each row of the matrix table the number will be the max of distincts countries). After that... both measures in the total need to have the same number so in the measure of the concatenate countries i have done a simple IF like.. if (distincount = maxdistincount, blank(), concatenatetext function)



Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

View solution in original post

12 REPLIES 12

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

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