Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

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 ...
DAX
  • v-sdhruv's avatar
    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.



bhanu_gautam's avatar
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
)