Creating summary Table with different filters

[iddy]

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.

[v-yueyunzh-msft · Accepted 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

[iddy]

Wow that is amazing! Thanks very much

[vaibhavkale570]

Are you expecting this result?

[iddy]

Hi, yes that looks about right based on limited data.

Can you please share how you did it?

[vaibhavkale570]

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.

[iddy]

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?

[amitchandak]

@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

[iddy]

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.