Forum Discussion
I want a DAX Query to create a pivot calculated table
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.
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.
Advocate IHi 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!