DAX distinct list of TOPN values

[dicken]

Hi,
I want to concatenate the items in a TOPN table column, and this is fine, but I sometimes have duplicate items,
so if I wanted the top 3 days in a month by date, these might all be Saturday, which gives me a concat of Sat Sat Sat , and ideally I'd just
like to see it once, This is just mock up example so

Top3C:=VAR tops =  TOPN(3,
                  SUMMARIZE(Table1,Table1[Country],Table1[Product]),[Total],DESC) 
                                RETURN CONCATENATEX(tops, Table1[Country],",")

So what I'm looking for is something like ;

CONCATENATEX(  VALUES( tops[Column] ) , Column, ",")

Richard.

 

[Matt Allington]

Why do you have products in the summarize table? This could definitely increase the chance of the country being repeated. Ie USA, Bikes, then USA Clothing are number 1 and 2, but you are only extracting the country. I’m guessing, but you could wrap another summarize around “tops” and extract just the country, then wrap that with a distinct, then do the concatenate.

 

[dicken]

Why do you have products in the summarize table? This could definitely increase the chance of the country being repeated. Ie USA, Bikes, then USA Clothing are number 1 and 2, but you are only extracting the country. I’m guessing, but you could wrap another summarize around “tops” and extract just the country, then wrap that with a distinct, then do the concatenate.

Because that is what I want to summarize by, the country is only there so I can then concatenate the top country for each product.
My original scenario was more like this ; I created a summarized table of sales by sales ID and day of week,
so I had the top 3 sales by sales ID, I then wanted the day name those sales were made on, but if the they were all
made on say Saturday, I only need to see that once not repeated 3 times.

Topday by Index:=VAR tops = TOPN( 3, SUMMARIZE(Table1,Table1[Index],'Calendar'[Day Of Week]),[TotalUnits],DESC)
RETURN CONCATENATEX( tops, 'Calendar'[Day Of Week], ", " )

This is then dropped into a pivot of Year Month Grouping and does show what I want , the top 3 days in a month,
but as said I only need to see each distinct day, if top 3 days were Sat 1st , 8th and 15th, I just need Saturday once.
I'll try another summarize around the topn table as i have not tried that yet,

Richard.

 

[dicken]

Why do you have products in the summarize table? This could definitely increase the chance of the country being repeated. Ie USA, Bikes, then USA Clothing are number 1 and 2, but you are only extracting the country. I’m guessing, but you could wrap another summarize around “tops” and extract just the country, then wrap that with a distinct, then do the concatenate.

Just to let you know the double summarize worked, I still haven't grasped why but here it is;

Distinct top day:=VAR tops = TOPN( 3, SUMMARIZE(Table1,Table1[Index],'Calendar'[Day Of Week]) ,[TotalUnits],DESC) 

VAR sumt  = SUMMARIZE( tops, 'Calendar'[Day Of Week]) 

RETURN CONCATENATEX(sumt, 'Calendar'[Day Of Week],",  ")

So now instead of say Sat, Sat, Sun, I just have Sat, Sun.

Thanks for the help.

Richard.