The ultimate Microsoft Fabric, Power BI, Azure AI, and SQL learning event! Join us in Stockholm, Sweden from September 24-27, 2024.
2-for-1 sale on June 20 only!
Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
Hi All,
I need help in creating some sort of a summary table based on the main table.
I have a data table where it has a list of tickets (incidents) with dates of when it is opened and resolved.
I then used a simple formula to calculate the week number of each of the date columns.
I would like to create a separate summary table the counts how many tickets are opened and closed in each week.
The problem that I have is, there are 2 date/week columns, so a simple SUMMARIZE function will not do the job.
I have tried using Summarize and ADDCOLUMNS but not sure how tomake it so it only counts when the column = value of that row. See screenshot below of example where the filtering is done on an absolute value "43".
Here is a screenshot of the dataset (simplified).
PS. I'm quite a beginner in my PowerBI journey, so any help is appreciated 🙂
Not sure how I can attach an Excel file here, but hopefully the screenshots help.
Solved! Go to Solution.
Hi , @iddy
Here are the steps you can refer to :
(1)This is my test data :
(2)We can click "New Table" to create a table:
Table 2 = var _resolved =VALUES('Table'[week_number_resolved])
var _opened =VALUES( 'Table'[week_number_opened])
var _all =DISTINCT( UNION(_opened , _resolved))
return
ADDCOLUMNS(_all , "resolved_count" , var _number = [week_number_opened] return CALCULATE(COUNT('Table'[incident_number]) ,TREATAS({_number} , 'Table'[week_number_resolved] )) ,"opened_count" , var _number = [week_number_opened] return CALCULATE(COUNT('Table'[incident_number]) ,TREATAS({_number} , 'Table'[week_number_opened] )) )
(3)Then we can meet your need , tghe result is as follows:
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi , @iddy
Here are the steps you can refer to :
(1)This is my test data :
(2)We can click "New Table" to create a table:
Table 2 = var _resolved =VALUES('Table'[week_number_resolved])
var _opened =VALUES( 'Table'[week_number_opened])
var _all =DISTINCT( UNION(_opened , _resolved))
return
ADDCOLUMNS(_all , "resolved_count" , var _number = [week_number_opened] return CALCULATE(COUNT('Table'[incident_number]) ,TREATAS({_number} , 'Table'[week_number_resolved] )) ,"opened_count" , var _number = [week_number_opened] return CALCULATE(COUNT('Table'[incident_number]) ,TREATAS({_number} , 'Table'[week_number_opened] )) )
(3)Then we can meet your need , tghe result is as follows:
Best Regards,
Aniya Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Wow that is amazing! Thanks very much
Are you expecting this result?
Hi, yes that looks about right based on limited data.
Can you please share how you did it?
1. in power query editor create two reference tables each for resolved and open week number
-remove open at and weeknumber open column from the resolved reference table
and group by the week_number_resolved
-remove resolved and week_number_resolved column from the Open reference table and group by the week_number_opened
2.you will get the below resulting tables
3. now make left join of these two tables based on the basis of week number column
4. select these option and click ok
and this is your resulting table
and you can disable enable load for other two columns
do some workaround in above steps to get your result.
ah...you used Power Query. Thanks for the details.
I should have mentioned that I was trying to avoid using PowerQuery because I would have to re-do the calculations there. (eg Weeknumber was calculation in DAX).
Any ideas if this can be done in DAX?
@iddy , use group by in place of SUMMARIZE
refer my DAX example in this blog
https://amitchandak.medium.com/power-bi-power-query-vs-dax-append-and-summarize-data-233f173d0839
Thanks for the reply - though I'm not sure how this can be used for my problem.
The issue I'm having is not the first grouping, rather appending the next column, which is grouping the count by another column based on the row value of the newly created table.
Not sure if I can explain it well here.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Check out the June 2024 Power BI update to learn about new features.
User | Count |
---|---|
102 | |
96 | |
80 | |
62 | |
56 |
User | Count |
---|---|
256 | |
120 | |
112 | |
83 | |
71 |