Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Dynamic results based on slicer selection

Hi Community Experts,

I am looking for help with this data source in Power BI.

Now, based on how i select the start date and end date on slicer "Date Selection" which comes from first column "Date", I want it to show the number of employees who were away in days away bracket such as 0-3 days, 4-5 days or >5 days. And the results could be different, as you see below, based on date selection

Please help demonstrate or enlist steps. Power Bi file attached here:- https://drive.google.com/file/d/1fQEx2thrmHiGog3Oix-okksnwK9MdNAd/view?usp=sharing

Need Help
  • nandukrishnavs's avatar
    nandukrishnavs
    6 years ago

    Anonymous

     

    I have tried to implement your scenario.

    Created a static table. [Category]

     

     

    Created a DAX measure

     

     

    Employees = 
VAR __Table =
    SUMMARIZE (
        RawData,
        RawData[Employee ID],
        "Count", COUNT ( RawData[Employee ID] )
    )
VAR __TableWithGroup =
    ADDCOLUMNS (
        __Table,
        "Days Group", SWITCH (
            TRUE (),
            [Count] > 0
                && [Count] <= 3, "0-3",
            [Count] > 3
                && [Count] <= 5, "4-5",
            ">5"
        )
    )
VAR __selectedGroup =
    SELECTEDVALUE ( Category[Days Away Group] )
VAR __TotalEmployees =
    COUNTX ( __Table, [Employee ID] )
VAR __CategoryWiseCount =
    COUNTX (
        FILTER ( __TableWithGroup, [Days Group] = __selectedGroup ),
        RawData[Employee ID]
    )
VAR __result =
    IF ( ISBLANK ( __selectedGroup ), __TotalEmployees, __CategoryWiseCount )
RETURN
    IF ( ISBLANK ( __result ), 0, __result )

     

     

    Use the Days Away Group column from Category table and newly created measure into the table visual

     

     

     

     

     Attaching the PBIX file for your reference.



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

7 Replies

  • nandukrishnavs's avatar
    nandukrishnavs
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous

     

    I have tried to implement your scenario.

    Created a static table. [Category]

     

     

    Created a DAX measure

     

     

    Employees = 
VAR __Table =
    SUMMARIZE (
        RawData,
        RawData[Employee ID],
        "Count", COUNT ( RawData[Employee ID] )
    )
VAR __TableWithGroup =
    ADDCOLUMNS (
        __Table,
        "Days Group", SWITCH (
            TRUE (),
            [Count] > 0
                && [Count] <= 3, "0-3",
            [Count] > 3
                && [Count] <= 5, "4-5",
            ">5"
        )
    )
VAR __selectedGroup =
    SELECTEDVALUE ( Category[Days Away Group] )
VAR __TotalEmployees =
    COUNTX ( __Table, [Employee ID] )
VAR __CategoryWiseCount =
    COUNTX (
        FILTER ( __TableWithGroup, [Days Group] = __selectedGroup ),
        RawData[Employee ID]
    )
VAR __result =
    IF ( ISBLANK ( __selectedGroup ), __TotalEmployees, __CategoryWiseCount )
RETURN
    IF ( ISBLANK ( __result ), 0, __result )

     

     

    Use the Days Away Group column from Category table and newly created measure into the table visual

     

     

     

     

     Attaching the PBIX file for your reference.



    Did I answer your question? Mark my post as a solution!
    Appreciate with a kudos     🙂

  • v-alq-msft's avatar
    v-alq-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi, Anonymous 

     

    Based on your description, you may create a new table as below. The pbix file is attached in the end.

    Table:

     

    You may create a measure as below.

    Employees = 
var tab = 
SUMMARIZE(
    ALLSELECTED(RawData),
    RawData[Employee ID],
    "Num",DISTINCTCOUNT(RawData[Date])
)
var newtab = 
ADDCOLUMNS(
    'Table',
    "Count",
    SWITCH(
        [Days Away Group],
        "0-3",
        COUNTROWS(
            FILTER(
                tab,
                [Num]>=0&&[Num]<=3
            )
        ),
        "4-5",
        COUNTROWS(
            FILTER(
                tab,
                [Num]>=4&&[Num]<=5
            )
        ),
        ">5",
        COUNTROWS(
            FILTER(
                tab,
                [Num]>5
            )
        )
    )
)
var result=
SUMX(
    newtab,
    [Count]
)
return
IF(
    ISBLANK(result),
    0,
    result
)

     

    Result:

     

     

    Best Regards

    Allan

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi Community Experts,

    I have this data below from my source. 

    I want a slicer on my Date field and based upon that slicer selection, i want a table which would tell me how many employees employees were away on travel for 0-3 days or 4-5 days or >5 days. Practical scenario of above example below based on different date slicer selection-

     

    Date Selection Slicer  Date Selection Slicer 
    Start DateEnd Date Start DateEnd Date
    10/21/201911/23/2019 10/21/201911/21/2019
         
    Days Away GroupEmployees Days Away GroupEmployees
    0-30 0-30
    4-50 4-51
    >52 >51
     

    I am also attaching the Power BI file, would be great if someone can help demonstrate or enlist steps here for me to follow. Thanks

    https://drive.google.com/file/d/1fQEx2thrmHiGog3Oix-okksnwK9MdNAd/view?usp=sharing