Problem creating Dynamic Table Based on Counting Rows by date

Hi!

Im trying to create a Table on Dax Where dynimically depending de date and the state that i select on my filters i obtain the counting of the rows that acomplish that conditions.

Here's my approach using ADDCOLUMNS, CROSSJOIN, and dynamic selection via SWITCH. Any ideas on performance or cleaner alternatives?

Case Count by Status and Duration Range =
VAR ReferenceDate =
IF(
HASONEVALUE(Calendar[DateID]),
MAX(Calendar[DateID]),
MAXX(ALL(Calendar), Calendar[DateID])
)
VAR CurrentYear = YEAR(ReferenceDate)
VAR CurrentMonth = MONTH(ReferenceDate)

RETURN
ADDCOLUMNS(
CROSSJOIN(
VALUES(StatusSelector[Status]),
VALUES(DurationRanges[Range])
),
"Status and Range Count",
VAR SelectedStatus = [Status]
VAR SelectedRange = [Range]

VAR CasesWithMetrics =
ADDCOLUMNS(
CaseHistory,
"days_in_status",
SWITCH(
SelectedStatus,
"Created", CaseHistory[Days_Created] + 0,
"Under Review", CaseHistory[Days_Review] + 0,
"Pending", CaseHistory[Days_Pending] + 0,
"In Process", CaseHistory[Days_Processing] + 0,
"Proposed", CaseHistory[Days_Proposed] + 0,
"Resolved", CaseHistory[Days_Resolved] + 0,
"Final Resolution", CaseHistory[Days_Notified] + 0,
"Closed", CaseHistory[Days_Total] + 0,
BLANK()
),
"status_date",
SWITCH(
SelectedStatus,
"Created", CaseHistory[Date_Created],
"Under Review", CaseHistory[Date_Review],
"Pending", CaseHistory[Date_Pending],
"In Process", CaseHistory[Date_Processing],
"Proposed", CaseHistory[Date_Proposed],
"Resolved", CaseHistory[Date_Resolved],
"Final Resolution", CaseHistory[Date_Notified],
"Closed", CaseHistory[Date_Closed],
BLANK()
)
)

Thanks a lot in advance 😄

RETURN
COUNTROWS(
FILTER(
CasesWithMetrics,
NOT ISBLANK([status_date]) &&
NOT ISBLANK([days_in_status]) &&
YEAR([status_date]) = CurrentYear &&
MONTH([status_date]) = CurrentMonth &&
[days_in_status] >= 1 &&
SWITCH(
TRUE(),
SelectedRange = "0-5", [days_in_status] < 5,
SelectedRange = "5-10", [days_in_status] >= 5 && [days_in_status] < 10,
SelectedRange = "10-15", [days_in_status] >= 10 && [days_in_status] < 15,
SelectedRange = ">15", [days_in_status] >= 15,
FALSE
)
)
)
)

DAX

v-sdhruv
1 year ago
Hi Anonymous ,

Maybe you want to check out this link-

https://community.fabric.microsoft.com/t5/Desktop/How-to-Count-Rows-in-a-Power-BI-Table-Based-on-Dynamically/m-p/4244851

or you can try implementing-

Case Count by Selected Status and Range =
VAR SelectedStatus = SELECTEDVALUE(StatusSelector[Status])
VAR SelectedYear = YEAR(MAX(Calendar[DateID]))
VAR SelectedMonth = MONTH(MAX(Calendar[DateID]))
VAR SelectedRange = SELECTEDVALUE(DurationRanges[Range])

RETURN
IF(
ISBLANK(SelectedStatus) || ISBLANK(SelectedRange),
BLANK(),
VAR FilteredTable =
ADDCOLUMNS(
CaseHistory,
"duration",
SWITCH(
SelectedStatus,
"Created", CaseHistory[Days_Created],
"Review", CaseHistory[Days_Review],
"Pending", CaseHistory[Days_Pending],
"Instruction", CaseHistory[Days_Instruction],
"Proposal", CaseHistory[Days_Proposal],
"Resolved", CaseHistory[Days_Resolved],
"Resolution", CaseHistory[Days_Resolution],
"Finalized", CaseHistory[Days_Finalized],
BLANK()
),
"status_date",
SWITCH(
SelectedStatus,
"Created", CaseHistory[Date_Created],
"Review", CaseHistory[Date_Review],
"Pending", CaseHistory[Date_Pending],
"Instruction", CaseHistory[Date_Instruction],
"Proposal", CaseHistory[Date_Proposal],
"Resolved", CaseHistory[Date_Resolved],
"Resolution", CaseHistory[Date_Resolution],
"Finalized", CaseHistory[Date_Finalized],
BLANK()
)
)
RETURN
COUNTROWS(
FILTER(
FilteredTable,
NOT ISBLANK([status_date]) &&
NOT ISBLANK([duration]) &&
YEAR([status_date]) = SelectedYear &&
MONTH([status_date]) = SelectedMonth &&
[duration] >= 1 &&
SWITCH(
TRUE(),
SelectedRange = "0-5", [duration] < 5,
SelectedRange = "5-10", [duration] >= 5 && [duration] < 10,
SelectedRange = "10-15", [duration] >= 10 && [duration] < 15,
SelectedRange = ">15", [duration] >= 15,
FALSE
)
)
)
)

Hope this helps!

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

8 Replies

v-sdhruv
Community Support
1 year ago
Hi Anonymous ,

Maybe you want to check out this link-

https://community.fabric.microsoft.com/t5/Desktop/How-to-Count-Rows-in-a-Power-BI-Table-Based-on-Dynamically/m-p/4244851

or you can try implementing-

Case Count by Selected Status and Range =
VAR SelectedStatus = SELECTEDVALUE(StatusSelector[Status])
VAR SelectedYear = YEAR(MAX(Calendar[DateID]))
VAR SelectedMonth = MONTH(MAX(Calendar[DateID]))
VAR SelectedRange = SELECTEDVALUE(DurationRanges[Range])

RETURN
IF(
ISBLANK(SelectedStatus) || ISBLANK(SelectedRange),
BLANK(),
VAR FilteredTable =
ADDCOLUMNS(
CaseHistory,
"duration",
SWITCH(
SelectedStatus,
"Created", CaseHistory[Days_Created],
"Review", CaseHistory[Days_Review],
"Pending", CaseHistory[Days_Pending],
"Instruction", CaseHistory[Days_Instruction],
"Proposal", CaseHistory[Days_Proposal],
"Resolved", CaseHistory[Days_Resolved],
"Resolution", CaseHistory[Days_Resolution],
"Finalized", CaseHistory[Days_Finalized],
BLANK()
),
"status_date",
SWITCH(
SelectedStatus,
"Created", CaseHistory[Date_Created],
"Review", CaseHistory[Date_Review],
"Pending", CaseHistory[Date_Pending],
"Instruction", CaseHistory[Date_Instruction],
"Proposal", CaseHistory[Date_Proposal],
"Resolved", CaseHistory[Date_Resolved],
"Resolution", CaseHistory[Date_Resolution],
"Finalized", CaseHistory[Date_Finalized],
BLANK()
)
)
RETURN
COUNTROWS(
FILTER(
FilteredTable,
NOT ISBLANK([status_date]) &&
NOT ISBLANK([duration]) &&
YEAR([status_date]) = SelectedYear &&
MONTH([status_date]) = SelectedMonth &&
[duration] >= 1 &&
SWITCH(
TRUE(),
SelectedRange = "0-5", [duration] < 5,
SelectedRange = "5-10", [duration] >= 5 && [duration] < 10,
SelectedRange = "10-15", [duration] >= 10 && [duration] < 15,
SelectedRange = ">15", [duration] >= 15,
FALSE
)
)
)
)

Hope this helps!

If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.
- Anonymous
  Not applicable
  1 year ago
  Thanks! This acomplish my objetive 🙂
- 123abc
  Community Champion
  1 year ago
  Excellent.👌
bhanu_gautam
Super User
1 year ago
Anonymous , Try using

dax
Case Count by Status and Duration Range =
VAR ReferenceDate =
IF(
HASONEVALUE(Calendar[DateID]),
MAX(Calendar[DateID]),
MAXX(ALL(Calendar), Calendar[DateID])
)
VAR CurrentYear = YEAR(ReferenceDate)
VAR CurrentMonth = MONTH(ReferenceDate)

VAR CasesWithMetrics =
ADDCOLUMNS(
CaseHistory,
"days_in_status",
SWITCH(
TRUE(),
CaseHistory[Status] = "Created", CaseHistory[Days_Created],
CaseHistory[Status] = "Under Review", CaseHistory[Days_Review],
CaseHistory[Status] = "Pending", CaseHistory[Days_Pending],
CaseHistory[Status] = "In Process", CaseHistory[Days_Processing],
CaseHistory[Status] = "Proposed", CaseHistory[Days_Proposed],
CaseHistory[Status] = "Resolved", CaseHistory[Days_Resolved],
CaseHistory[Status] = "Final Resolution", CaseHistory[Days_Notified],
CaseHistory[Status] = "Closed", CaseHistory[Days_Total],
BLANK()
),
"status_date",
SWITCH(
TRUE(),
CaseHistory[Status] = "Created", CaseHistory[Date_Created],
CaseHistory[Status] = "Under Review", CaseHistory[Date_Review],
CaseHistory[Status] = "Pending", CaseHistory[Date_Pending],
CaseHistory[Status] = "In Process", CaseHistory[Date_Processing],
CaseHistory[Status] = "Proposed", CaseHistory[Date_Proposed],
CaseHistory[Status] = "Resolved", CaseHistory[Date_Resolved],
CaseHistory[Status] = "Final Resolution", CaseHistory[Date_Notified],
CaseHistory[Status] = "Closed", CaseHistory[Date_Closed],
BLANK()
)
)

RETURN
SUMX(
FILTER(
CasesWithMetrics,
NOT ISBLANK([status_date]) &&
NOT ISBLANK([days_in_status]) &&
YEAR([status_date]) = CurrentYear &&
MONTH([status_date]) = CurrentMonth &&
[days_in_status] >= 1 &&
SWITCH(
TRUE(),
[Range] = "0-5", [days_in_status] < 5,
[Range] = "5-10", [days_in_status] >= 5 && [days_in_status] < 10,
[Range] = "10-15", [days_in_status] >= 10 && [days_in_status] < 15,
[Range] = ">15", [days_in_status] >= 15,
FALSE
)
),
1
)
v-sdhruv
Community Support
1 year ago
Hi Anonymous ,
Just wanted to check if you had the opportunity to review the suggestions provided?
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.
Thank You
- Anonymous
  Not applicable
  1 year ago
  Hi!
  Sorry i did not replay earlier, my mail did not notified me, First of all thanks bhanu_gautam, but unfortunetly it did not work 😅 the problem is that i have 3 tables not only 1 y mistaken when i wrote it
  So the problem is i dont have CaseHistory[Status] in my original data, what im trying to create is a table thats insert the data from CaseHistory[] delimited by the two conditions from StatusSelector[Status], DurationRanges[Range], both Auxiliar tables.
  And my objetive is to gather dynimically the data in that table, that will show the information once is filtered by the two auxiliar tables.
  An example will be this: If i Select "Created" the table will show me this:
  
  Result Range
  1 0-5
  1 >15
  
  Actually i have a Measure that can do it, but i can not acomplish with the table:
  Measure =
  VAR selectedStatus = SELECTEDVALUE(StatusSelector[Status])
  VAR yearSelected = YEAR(MAX(Calendar[DateID]))
  VAR monthSelected = MONTH(MAX(Calendar[DateID]))
  VAR selectedRange = SELECTEDVALUE(DurationRanges[Range])
  RETURN
  COUNTROWS(
  FILTER(
  MainTable,
  VAR duration = SWITCH(
  selectedStatus,
  "Created", MainTable[Days_Created],
  "Under Review", MainTable[Days_Review],
  "Pending", MainTable[Days_Pending],
  "In Process", MainTable[Days_Process],
  "Proposed", MainTable[Days_Proposal],
  "Resolved", MainTable[Days_Resolved],
  "Notified", MainTable[Days_Notified],
  "Closed", MainTable[Days_Total]
  )
  VAR date = SWITCH(
  selectedStatus,
  "Created", MainTable[Date_Created],
  "Under Review", MainTable[Date_Review],
  "Pending", MainTable[Date_Pending],
  "In Process", MainTable[Date_Process],
  "Proposed", MainTable[Date_Proposal],
  "Resolved", MainTable[Date_Resolved],
  "Notified", MainTable[Date_Notified],
  "Closed", MainTable[Date_Closed]
  )
  RETURN
  YEAR(date) = yearSelected &&
  MONTH(date) = monthSelected &&
  duration >= 1 &&
  SWITCH(
  TRUE(),
  selectedRange = "0-5", duration < 5,
  selectedRange = "5-10", duration >= 5 && duration < 10,
  selectedRange = "10-15", duration >= 10 && duration < 15,
  selectedRange = ">15", duration >= 15,
  BLANK()
  )
  )
  
  Thanks in advance
v-sdhruv
Community Support
1 year ago
Hi Anonymous ,
You can try this in calculated table-
Go to modelling->New Table

Case Count by Status and Range =
VAR SelectedStatus = SELECTEDVALUE(StatusSelector[Status])
VAR SelectedYear = YEAR(MAX(Calendar[DateID]))
VAR SelectedMonth = MONTH(MAX(Calendar[DateID]))

RETURN
ADDCOLUMNS(
VALUES(DurationRanges[Range]),
"Result",
VAR SelectedRange = [Range]

VAR FilteredTable =
ADDCOLUMNS(
CaseHistory,
"duration",
SWITCH(
SelectedStatus,
"Created", CaseHistory[Days_Created],
"Under Review", CaseHistory[Days_Review],
"Pending", CaseHistory[Days_Pending],
"In Process", CaseHistory[Days_Process],
"Proposed", CaseHistory[Days_Proposal],
"Resolved", CaseHistory[Days_Resolved],
"Notified", CaseHistory[Days_Notified],
"Closed", CaseHistory[Days_Total],
BLANK()
),
"status_date",
SWITCH(
SelectedStatus,
"Created", CaseHistory[Date_Created],
"Under Review", CaseHistory[Date_Review],
"Pending", CaseHistory[Date_Pending],
"In Process", CaseHistory[Date_Process],
"Proposed", CaseHistory[Date_Proposal],
"Resolved", CaseHistory[Date_Resolved],
"Notified", CaseHistory[Date_Notified],
"Closed", CaseHistory[Date_Closed],
BLANK()
)
)

RETURN
COUNTROWS(
FILTER(
FilteredTable,
NOT ISBLANK([status_date]) &&
NOT ISBLANK([duration]) &&
YEAR([status_date]) = SelectedYear &&
MONTH([status_date]) = SelectedMonth &&
[duration] >= 1 &&
SWITCH(
TRUE(),
SelectedRange = "0-5", [duration] < 5,
SelectedRange = "5-10", [duration] >= 5 && [duration] < 10,
SelectedRange = "10-15", [duration] >= 10 && [duration] < 15,
SelectedRange = ">15", [duration] >= 15,
FALSE
)
)
)
)
If possible can you also share a sample file?

How to provide sample data in the Power BI Forum

You can refer the following link to upload the file to the community.
How to upload PBI in Community
If the response has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.
- Anonymous
  Not applicable
  1 year ago
  It Does not work, im attaching some examples of my data:
  
  Range:
  Range Order
  0–5 1
  5–10 2
  10–15 3
  >15 4
  
  States:
  Status Order
  Created 1
  Review 2
  Pending 3
  Instruction 4
  Proposal 5
  Resolved 6
  Resolution 7
  Finalized 8
  
  CaseHistory:
  
  What i want to re-create:
  Selected: Created
  Selected: Year 2025
  
  With the answer you propoused me i only get blanks, dunno whats wrong... Sorry

Forum Discussion

Problem creating Dynamic Table Based on Counting Rows by date

8 Replies

Related content

Counting nulls

Create count measure

Logic problem?

Slider problems

Row count problem in table

Created	1
Review	2
Pending	3
Instruction	4
Proposal	5
Resolved	6
Resolution	7
Finalized	8

Result	Range
1	0-5
1	>15

0–5	1
5–10	2
10–15	3
>15	4