Forum Discussion
I want a DAX Query to create a pivot calculated table
I have a table as below
| Unique ID | Name | Equipment |
| A998877 | Alex | Laptop |
| B554223 | Bob | Mobile |
| K981265 | Alex | Laptop |
| G674320 | Julia | Desktop |
| F437612 | Smith | Laptop |
| Y764378 | Alex | Mobile |
From this table i want to have a pivot table as below, please use Claculated DAX measure to create a pivot table, because i have created calculated columns , which cannot be used in power query.
| Name | Laptop | Mobile | Desktop |
| Alex | 2 | 1 | |
| Bob | 1 | ||
| Julia | 1 | ||
| Smith | 1 |
Hi Anonymous ,
Thanks for the reply from miTutorials , please allow me to provide another insight:
You can try the following DAX formula:
SummaryTable = SUMMARIZE( 'Table', 'Table'[Name], "Laptop", CALCULATE(COUNTROWS('Table'), 'Table'[Equipment] = "Laptop"), "Mobile", CALCULATE(COUNTROWS('Table'), 'Table'[Equipment] = "Mobile"), "Desktop", CALCULATE(COUNTROWS('Table'), 'Table'[Equipment] = "Desktop") )If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
5 Replies
miTutorialsSuper User
Why do you need dax when this can be achieved using Matrix ?
Hi Anonymous ,
Thanks for the reply from miTutorials , please allow me to provide another insight:
You can try the following DAX formula:
SummaryTable = SUMMARIZE( 'Table', 'Table'[Name], "Laptop", CALCULATE(COUNTROWS('Table'), 'Table'[Equipment] = "Laptop"), "Mobile", CALCULATE(COUNTROWS('Table'), 'Table'[Equipment] = "Mobile"), "Desktop", CALCULATE(COUNTROWS('Table'), 'Table'[Equipment] = "Desktop") )If your Current Period does not refer to this, please clarify in a follow-up reply.
Best Regards,
Clara Gong
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
kshethAdvocate I
Hi Anonymous ,
Not the original poster, but I was trying to do something similar. This code mostly worked for what I needed, but I need to additionally filter my original table by a calculated column ("InCity").
Original table:
VisitID Date Sex InCity A 1/1/2024 Male
Y B 1/1/2024 Male Y C 1/2/2024 Female Y D 1/3/2024 Male N E 1/3/2024 Female Y F 1/3/2024 Female N G 1/4/2024 Male
Y H 1/4/2024 Female Y And this is the table I want to create, where it includes counts of VisitID but only when InCity = Y:
Date Male Female 1/1/2024 2 1/2/2024 1 1/3/2024 1 1/4/2024 1 1 I would really appreciate any guidance you can provide to update the code to include a filter for InCity=Y. Thank you!