topic Re: Problem creating Dynamic Table Based on Counting Rows by date in DAX Commands and Tips

Problem creating Dynamic Table Based on Counting Rows by date

Anonymous — Fri, 23 May 2025 10:10:45 GMT

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
)
)
)
)

Re: Problem creating Dynamic Table Based on Counting Rows by date

bhanu_gautam — Fri, 23 May 2025 11:24:53 GMT

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
)

Re: Problem creating Dynamic Table Based on Counting Rows by date

v-sdhruv — Mon, 26 May 2025 05:16:56 GMT

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

Re: Problem creating Dynamic Table Based on Counting Rows by date

Anonymous — Mon, 26 May 2025 07:02:47 GMT

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

Re: Problem creating Dynamic Table Based on Counting Rows by date

v-sdhruv — Mon, 26 May 2025 10:04:38 GMT

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.

Re: Problem creating Dynamic Table Based on Counting Rows by date

Anonymous — Mon, 26 May 2025 10:33:19 GMT

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

Re: Problem creating Dynamic Table Based on Counting Rows by date

v-sdhruv — Tue, 27 May 2025 12:38:57 GMT

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.

Re: Problem creating Dynamic Table Based on Counting Rows by date

Anonymous — Thu, 29 May 2025 06:03:35 GMT

Thanks! This acomplish my objetive 🙂

Re: Problem creating Dynamic Table Based on Counting Rows by date

123abc — Thu, 29 May 2025 08:35:49 GMT

Excellent.👌