cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Regular Visitor

## PowerBI Calculate and show in report the total number of occurrences for individual entries

Hello everyone,

First of all thank you for your support and consideration.

I am fairly new to PowerBI and I need to create a report that would show relevant information about the data.

The data which is coming in PowerBI is structured like this and also pasted some sample data:

Container  |  IssueName  |  IssueStatus  |  TimeCreated

Cont1        |  IssueX         |  OPEN           |   10/13/2021

Cont1        |  IssueY         |  OPEN           |   10/15/2021

Cont1        |  IssueA         |  CLOSED       |   10/15/2021

Cont2        |  IssueZ         |  OPEN           |   10/15/2021

Cont2        |  IssueB         |  CLOSED       |   10/17/2021

Cont3        |  IssueY         |  OPEN           |   10/15/2021

Cont3        |  IssueY         |  OPEN           |   10/15/2021

What I would like to do is to report for each container how many OPEN or CLOSED issues are present.

Thank you,

Best regards,

Nick.

1 ACCEPTED SOLUTION
Super User

@NickCode , with an independent date table to select date or month

Opened = calculate(distinctCOUNT('Table'[Container]), 'Table'[IssueStatus]="Open")
Closed calculate(distinctCOUNT('Table'[Container]), 'Table'[IssueStatus]="Closed")

Open not closed on a selected date =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate([Opened] - [Closed], filter('Table', 'Table'[TimeCreated] <= _max))

Month Opened =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate(distinctCOUNT('Table'[Container]), filter('table', 'Table'[Date] >=_min && 'Table'[Date] <=_max && 'Table'[IssueStatus]="Open"))

month closed =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate(distinctCOUNT('Table'[Container]), filter('table', 'Table'[Date] >=_min && 'Table'[Date] <=_max && 'Table'[IssueStatus]="Closed"))

Super User

@NickCode , with an independent date table to select date or month

Opened = calculate(distinctCOUNT('Table'[Container]), 'Table'[IssueStatus]="Open")
Closed calculate(distinctCOUNT('Table'[Container]), 'Table'[IssueStatus]="Closed")

Open not closed on a selected date =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate([Opened] - [Closed], filter('Table', 'Table'[TimeCreated] <= _max))

Month Opened =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate(distinctCOUNT('Table'[Container]), filter('table', 'Table'[Date] >=_min && 'Table'[Date] <=_max && 'Table'[IssueStatus]="Open"))

month closed =
var _max = maxx(allselected('Date1'), 'Date1'[Date])
var _min = maxx(allselected('Date1'), 'Date1'[Date])
return
calculate(distinctCOUNT('Table'[Container]), filter('table', 'Table'[Date] >=_min && 'Table'[Date] <=_max && 'Table'[IssueStatus]="Closed"))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.