Summarized table in Power BI

[Rinku21]

Hi,

I am trying to create a summarized table but facing some issues. 

Below table is the main table and I need to create a summarized table in which there should be only one region for each Id.

Id	Region
1	APAC
1	APAC
1	CHN
2	APAC
2	EMEA
3	NAMER
3	NAMER

The output should be as below:

Id	Region
1	APAC
2	APAC/EMEA
3	NAMER

For Id 1, Occurances of region 'APAC' (2), CHN(1), so the output should be : APAC

For Id 2, Occurances of region 'APAC' (1), EMEA (1), the count is similar so the output should be any random region. Either 'APAC' or 'EMEA'

For Id 3, Occurances Region NAMER (2), so output should be : NAMER

Can anyone please help me on that?

Thanks,

Poonam

[v-kelly-msft · Accepted solution]

Hi @Rinku21,

First create a calculated column as below:

Count = CALCULATE(COUNT('Table'[Region]),FILTER(ALL('Table'),'Table'[Id]=EARLIER('Table'[Id])&&'Table'[Region]=EARLIER('Table'[Region])))

Then create a measure as below:

_Region = 
VAR _distinctcount1=CALCULATE(DISTINCTCOUNT('Table'[Count]),FILTER(ALL('Table'),'Table'[Id]=MAX('Table'[Id])))
var _distinctcount2=CALCULATE(DISTINCTCOUNT('Table'[Region]),FILTER(ALL('Table'),'Table'[Id]=MAX('Table'[Id])))
Return
IF(_distinctcount1>1||_distinctcount2=1,CALCULATE(MAX('Table'[Region]),FILTER(ALL('Table'),'Table'[Id]=MAX('Table'[Id])&&'Table'[Count]=MAXX(FILTER(ALL('Table'),'Table'[Id]=MAX('Table'[Id])),'Table'[Count]))),CONCATENATEX(DISTINCT('Table'[Region]),'Table'[Region],"/")
)

Finally you will see:

For details,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Rinku21]

Works perfect! Appreciate your reply.

Thanks,

Rinku

[amitchandak]

@Rinku21 , Try like

concatenatex(summarize(Table,Table[ID], Table[Region]),[Region])

https://docs.microsoft.com/en-us/dax/concatenatex-function-dax

[Rinku21]

Hi @amitchandak ,

Thanks for your reply. I need to select the maximum occured region in each row.

As mentioned above, it should be :

Id	Region
1	APAC
2	APAC/EMEA
3	NAMER

I tried, concatenatex(summarize(Table,Table[ID], Table[Region]),[Region]) but its not working.

It seems we need to use RANKX function. I tried below DAX function. Now, I need to select a region with max count for each row.

Do you have any idea how it can be achieved?

Thank you!