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.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |