Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
GROUP | ITEM | DATE | COUNTRY |
PRIORITY | 1 | 1/1/2019 | SPAIN |
PRIORITY | 1 | 2/1/2019 | SPAIN |
PRIORITY | 1 | 3/1/2019 | SPAIN |
PRIORITY | 1 | 4/1/2019 | SPAIN |
PRIORITY | 1 | 5/1/2019 | SPAIN |
PRIORITY | 2 | 1/1/2019 | FRANCE |
PRIORITY | 2 | 2/1/2019 | FRANCE |
PRIORITY | 2 | 3/1/2019 | FRANCE |
PRIORITY | 2 | 4/1/2019 | FRANCE |
PRIORITY | 2 | 5/1/2019 | FRANCE |
NOT PRIORITY | 3 | 1/1/2019 | EEUU |
NOT PRIORITY | 3 | 2/1/2019 | EEUU |
NOT PRIORITY | 3 | 3/1/2019 | EEUU |
NOT PRIORITY | 3 | 4/1/2019 | EEUU |
NOT PRIORITY | 3 | 5/1/2019 | EEUU |
NOT PRIORITY | 4 | 1/1/2019 | GERMANY |
NOT PRIORITY | 4 | 2/1/2019 | GERMANY |
NOT PRIORITY | 4 | 3/1/2019 | GERMANY |
NOT PRIORITY | 4 | 4/1/2019 | GERMANY |
NOT PRIORITY | 4 | 5/1/2019 | GERMANY |
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
GROUP | ITEM | COUNTRY |
NOT PRIORITY | 3 | EEUU |
NOT PRIORITY | 4 | GERMANY |
NOT PRIORITY Total | EEUU, GERMANY | |
PRIORITY | 1 | SPAIN |
PRIORITY | 2 | FRANCE |
PRIORITY Total | SPAIN, FRANCE | |
Grand Total | EEUU, GERMANY,SPAIN, FRANCE |
when in reiality for the big total i want to see a blank
GROUP | ITEM | COUNTRY |
NOT PRIORITY | 3 | EEUU |
NOT PRIORITY | 4 | GERMANY |
NOT PRIORITY Total | EEUU, GERMANY | |
PRIORITY | 1 | SPAIN |
PRIORITY | 2 | FRANCE |
PRIORITY Total | SPAIN, FRANCE | |
Grand Total |
it could be possible?
Kind regards!!!
Solved! Go to 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)
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.
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())
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.
@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?
just disable the total for this table visual.
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.
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
GROUP | ITEM | COUNTRY | Revenue |
NOT PRIORITY | 3 | EEUU | $ 500.00 |
NOT PRIORITY | 4 | GERMANY | $ 600.00 |
NOT PRIORITY Total | EEUU, GERMANY | $ 1,100.00 | |
PRIORITY | 1 | SPAIN | $ 200.00 |
PRIORITY | 2 | FRANCE | $ 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
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).
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.
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 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)
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
User | Count |
---|---|
124 | |
108 | |
99 | |
62 | |
62 |
User | Count |
---|---|
137 | |
116 | |
102 | |
71 | |
61 |