How to extract few columns from a table and apply Group function on them ?

[kamran]

Hi All

My data is as below:

StudentID	Name	City	Main Ethnicity	Ethnicity2	Ethnicity3
1741262	F	Wellington	European	Pasifika	Māori
2085174	H	Wellington	Māori	European	European
1934579	X	Wellington
1274568	E	Wellington	Māori	Pasifika	Pasifika
2184074	H	Wellington	Māori	Pasifika	Pasifika
1061861	D	Wellington	NZ European/Pakeha	European	European
109241	A	Wellington	NZ European/Pakeha	European	Pasifika
109241	A	Wellington	NZ European/Pakeha	European	Pasifika
240739	B	Wellington	NZ European/Pakeha	Māori	Other
240739	B	Wellington	NZ European/Pakeha	Māori	Other
240739	B	Wellington	NZ European/Pakeha	Māori	Other
5859038	K	Wellington	NZ European/Pakeha	Māori	Other
1962443	G	Wellington	NZ European/Pakeha	Māori	Pasifika
2486795	I	Wellington	NZ European/Pakeha	Pasifika	Pasifika
2992062	J	Wellington	NZ European/Pakeha	Pasifika	Pasifika
2992062	J	Wellington	NZ European/Pakeha	Pasifika	Pasifika
423649	Y	Wellington
2992062	J	Wellington	NZ European/Pakeha	Pasifika	Pasifika
2992062	J	Wellington	NZ European/Pakeha	Pasifika	Pasifika
616474	C	Wellington	Pasifika	NZ European/Pakeha	Chinese
616474	C	Wellington	Pasifika	NZ European/Pakeha	Chinese

Out of this data, I wish to display a table having output as:

All Ethnicities	No. of Students
Chinese	1
European	6
Māori	6
NZ European/Pakeha	8
Other	2
Pasifika	11

Logic should be:

1. Display all the Ethnicities in All Ethnicities column (Distinct Union of values from 3 Ethicities columns).
2. Ignore the rows having Null Ethnicity.
3. Distinct Count of Students for each Ethnicity separately i.e Count on Main Ethnicity, then on Ethicity2 and then Ethicity3.
4. Finally display the total for all Ethnicities by Summing up individual totals in No. of Students column.

Is it possible in DAX?

Thanks

[v-lili6-msft]

HI,@kamran

    After my research, You can do these follow my steps as below:

Step1:

use this formula to create a table

Group table = 
var _table1=FILTER(SUMMARIZE('Table','Table'[Main Ethnicity],"No. of Students",DISTINCTCOUNT('Table'[StudentID])),'Table'[Main Ethnicity] <>BLANK()) return
var _table2=FILTER(SUMMARIZE('Table','Table'[Ethnicity2],"No. of Students",DISTINCTCOUNT('Table'[StudentID])),'Table'[Ethnicity2] <>BLANK()) return
var _table3=FILTER(SUMMARIZE('Table','Table'[Ethnicity3],"No. of Students",DISTINCTCOUNT('Table'[StudentID])),'Table'[Ethnicity3] <>BLANK()) return
var _uniontable=UNION(_table1,_table2,_table3) return
_uniontable

Step2:

Rename the column and drag fields into table visual

here is pbix, please try it.

https://www.dropbox.com/s/ruzky3x7g9wuus4/How%20to%20extract%20few%20columns%20from%20a%20table%20an...

Regards,

Lin

[kamran · Accepted solution]

Hi Lin

Thank you so much for your solution and my apologies for such a late response.

Actually no one replied on my query for first few days so I left to pursue it and found a solution by myself,

which was interestingly almost similar to yours one, but I created 3 different tables(data sets) using SUMMARIZE and then created union of them in a final dataset.

Both of these solutions are serving the purpose partially, because, though they calculate the Total Count of students on all Ethnicities, but rest of the Dashboard is not filtered when I click on an individual Ethinicity (for example Chinese) in the table visual.

I solved this issue as below:

Redesigned the individual datasets, by creating Normalized datasets without duplication as it's the requirement to join two datasets in DAX data model.

Firstly created a granular dataset which is StudentID in this case, then another Dataset with StudentID and their Ethnicities, 

and finally linked all datasets with StudentID using One-to-Many and Both directional filtering properties.

Now, the Count of StudentID is producing the totals and all the visuals are also being filtered mutually.

Thanks again

Kamran