Problem creating Dynamic Table Based on Counting Rows by date

[Cyos98]

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

[v-sdhruv · Accepted solution]

Hi @Cyos98 ,

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-Dyn...

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.

[123abc]

Excellent.👌

[Cyos98]

Thanks! This acomplish my objetive 🙂

[v-sdhruv]

Hi @Cyos98 ,
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.

[Cyos98]

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

[v-sdhruv]

Hi @Cyos98 ,
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

[Cyos98]

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

[bhanu_gautam]

@Cyos98 , 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
)