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.



v-sdhruv's avatar
v-sdhruv
Icon for Community Support rankCommunity 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's avatar
    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:

     

    ResultRange
    10-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