Forum Discussion

kamran's avatar
kamran
Frequent Visitor
7 years ago
Solved

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

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

 

Need Help
  • kamran's avatar
    kamran
    7 years ago

    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

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    7 years ago

    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%20and%20apply%20Group%20function%20on%20them.pbix?dl=0

     

    Regards,

    Lin

     

     

    • kamran's avatar
      kamran
      Frequent Visitor
      7 years ago

      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