March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I hope someone can help with this query, I have a requirement for a query summary the total number of issue by the users in the column "Solved by" and the total number of issue by the users in the column "Solved by (before)" then get the % between them.
This is what I have:
ID | Solved by | Solved by (before) | Repeated |
1 | mario | ami | SAME ISSUE |
2 | mario | jack | SAME ISSUE |
3 | mario | tom | SAME ISSUE |
4 | jack | tom | SAME ISSUE |
5 | jack | tom | SAME ISSUE |
6 | tom | ami | DIFFERENT ISSUE |
7 | jack | mario | DIFFERENT ISSUE |
8 | peter | mario | DIFFERENT ISSUE |
9 | tom | jack | DIFFERENT ISSUE |
10 | jack | jack | DIFFERENT ISSUE |
11 | mario | mario | SAME ISSUE |
12 | tom | tom | SAME ISSUE |
13 | tom | tom | SAME ISSUE |
14 | jack | peter | DIFFERENT ISSUE |
15 | ami | peter | DIFFERENT ISSUE |
16 | ami | jack | SAME ISSUE |
17 | ami | mario | SAME ISSUE |
18 | peter | mario | SAME ISSUE |
19 | peter | peter | SAME ISSUE |
20 | peter | jack | DIFFERENT ISSUE |
21 | peter | tom | DIFFERENT ISSUE |
This is what I want:
USER | Total ISSUE | Total SAME ISSUE | % SAME ISSUE | Total DIFFERENTE ISSUE | % DIFFERENT ISSUE |
mario | 4 | 3 | 75% | 2 | 50% |
jack | 5 | 2 | 40% | 3 | 60% |
tom | 4 | 5 | 125% | 1 | 25% |
peter | 5 | 1 | 20% | 2 | 40% |
ami | 3 | 0 | 0% | 1 | 33% |
I try get it by making measure:
Total ISSUE = COUNTROWS(DISTINCT(ID))--by "Solved by"
Total SAME ISSUE = COUNTROWS(DISTINCT(FILTER(Table1,Repeated="SAME ISSUE")))--by "Solved by (before)"
Total DIFFERENTE ISSUE = COUNTROWS(DISTINCT(FILTER(Table1,Repeated="DIFFERENTE ISSUE")))--by "Solved by (before)"
% SAME ISSUE = Total SAME ISSUE/Total ISSUE
% DIFFERENTE ISSUE = Total DIFFERENTE ISSUE/Total ISSUE
Then put it on a matrix, but it doesnt calculated by "solved by" and "solved by before".
Should I calculated it in a different table?
Any help is really appreciated. Thanks a lot
Maria J.
Solved! Go to Solution.
Hi @alfonsoasenjo ,
You should unpivot columns to:
Then you can use the following measure:
Total ISSUE = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Sovled] = "Solved by")
Total SAME ISSUE = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Sovled] = "Solved by (before)",'Table'[Repeated] = "SAME ISSUE")
Total DIFFERENTE ISSUE = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Sovled] = "Solved by (before)",'Table'[Repeated]="DIFFERENT ISSUE")
% SAME ISSUE = [Total SAME ISSUE]/[Total ISSUE]
% DIFFERENTE ISSUE = [Total DIFFERENTE ISSUE]/[Total ISSUE]
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZDK-TzJRSdGtxUQAH...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
Hi @alfonsoasenjo ,
You should unpivot columns to:
Then you can use the following measure:
Total ISSUE = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Sovled] = "Solved by")
Total SAME ISSUE = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Sovled] = "Solved by (before)",'Table'[Repeated] = "SAME ISSUE")
Total DIFFERENTE ISSUE = CALCULATE(DISTINCTCOUNT('Table'[ID]),'Table'[Sovled] = "Solved by (before)",'Table'[Repeated]="DIFFERENT ISSUE")
% SAME ISSUE = [Total SAME ISSUE]/[Total ISSUE]
% DIFFERENTE ISSUE = [Total DIFFERENTE ISSUE]/[Total ISSUE]
For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZDK-TzJRSdGtxUQAH...
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Best Regards,
Dedmon Dai
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.
User | Count |
---|---|
127 | |
85 | |
69 | |
53 | |
44 |
User | Count |
---|---|
202 | |
106 | |
100 | |
64 | |
56 |