Forum Discussion
How to extract few columns from a table and apply Group function on them ?
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
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.
Regards,
Lin
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