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
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
Ashish_Mathur
Super User
Super User

Hi,

This measure should work

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

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
dax
Community Support
Community Support

Hi dobregon,

You could try to use below measure to see whether it work ot not

Countries = var temp=
CONCATENATEX (
    SUMMARIZE (
        FILTER ( Metadata, Metadata[Country] <> BLANK () ),
        Metadata[Country]
    ),
    Metadata[Country],
    ","
)  return if(HASONEVALUE(Metadata[GROUP]), temp, BLANK())

446.PNG

Best Regards,
Zoe Zhi

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

 

dobregon
Impactful Individual
Impactful Individual

@dax thanks for the response, it is valid for this example. But in reality i have more type of grups like (priority and not priority, EMEA or NAMR, Bikes or motorbikes, etc) and maybe the table will have different type of groups and i'm looking for the option to detect the total value and put a blank() whatever group has the tablematrix. is it possible?



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

@dobregon 

just disable the total for this table visual.

Capture.PNG

 

regards,

Aditya Vighne

 

accept this as a solution if the issue is resolved.

@adityavighne  thanks for the response, but it is not valid for me becasue in reality the tablematrix will have then some values like revenue, count of items, etc that need to have total values.



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

@dobregon 

 

it's easy with matrics table 

still, you can achieve this in matrix just go to "subtotals" and unable "per row-level"

 

and then keep total which you want and disable which you don't want

HI @adityavighne , maybe i dont understand your point but it think it is not valid in my case. As i want to have is something like this

GROUPITEMCOUNTRYRevenue
NOT PRIORITY3EEUU $     500.00
NOT PRIORITY4GERMANY $     600.00
NOT PRIORITY Total EEUU, GERMANY $  1,100.00
PRIORITY1SPAIN $     200.00
PRIORITY2FRANCE $     300.00
PRIORITY Total SPAIN, FRANCE $     500.00
TOTAL   $  1,600.00

 

So, as you can see the total has values in revenue but i dont want in country. With your option the only level is Group and if we disable the total, will be disabled for all the columns. i have to want the total values for all the columns except order measures. It is important to know that the country column is a measure that concatenate the text, not a column added in the rows of the matrix table



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

Hi dobregon, 

I think this should work, when you don't want to see value in grand total, you could try to use my above measure(use parent group in if condition).

450.PNG

If you want to see value in grand total, you could use sum measure directly (instead of writing if condition). The country and revenue are different measures.

449.PNG

Best Regards,
Zoe Zhi

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

 

 

adityavighne
Continued Contributor
Continued Contributor

@dobregon 

you are looking for this result. just confirm

33.PNG

@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)
dax
Community Support
Community Support

Hi dobregon,

If your issue is solved please mark the appropriate answer as Mark as answer. This will help other members to find a solution if they face the same issue. If you still have any questions, please feel free to ask.

Thanks for your understanding and support.
Best Regards,
Zoe Zhi

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.