topic Re: Measure to get distinct countries from column holding a list of countries for each row in DAX Commands and Tips

Measure to get distinct countries from column holding a list of countries for each row

Anonymous — Fri, 24 Nov 2023 07:32:45 GMT

Hi,
Could someone help with the below scenario:
I have a table similar to below which holds a list of countries for each row:

ID	Group	Country
1011	A	INDIA;CHINA
1038	A	INDIA;CHINA;ENGLAND;SRILANKA
5052	B	SWEDEN;FINLAND
3033	C	SRILANKA
9800	B	SWEDEN;NORWAY
6500	B	NORWAY;FINLAND

Now I need to create a measure that gives me the distinct country based on the filters applied for 'ID' or 'Group' columns.

For example, if i apply the filter "Group = B", it should consider the 3 rows for group B and measure should give me the distinct count as 3. It will get the 3 distinct countries from the combination of those 3 rows (SWEDEN,NORWAY,FINLAND)

Could you please help me here.
In the real scenario the list is much longer.

Re: Measure to get distinct countries from column holding a list of countries for each row

Rupak_bi — Fri, 24 Nov 2023 10:03:00 GMT

Is this something you are looking for?

Re: Measure to get distinct countries from column holding a list of countries for each row

Anonymous — Fri, 24 Nov 2023 10:31:03 GMT

Thanks.
But I want to get the distinct count of the countries from the column 'Country' for the selected filters.
It has to be "3" when Group B is selected (SWEDEN,NORWAY,FINLAND) ,
"4" for Group A (INDIA, CHINA, ENGLAND, SRILANKA)
and "1" for Group C (Only SRILANKA).

Re: Measure to get distinct countries from column holding a list of countries for each row

Anonymous — Fri, 24 Nov 2023 10:57:54 GMT

Hi,

Below are the results as expected..

Firstly ., Need to Split the country column with a delimiter in power query editor.

Then create a measure..

Re: Measure to get distinct countries from column holding a list of countries for each row

Rupak_bi — Sat, 25 Nov 2023 05:20:18 GMT

Hi , Please see below

if you are looking for this then follow Anonymous answer.