Forum Discussion

ElliotK's avatar
ElliotK
Helper I
4 years ago

Count within a Slicer

Morning, 

 

I am creating summary table in order to use a column within a slicer that has a count on it, thereby having the count in the slicer. However, I cannot get the count to work:

 

Tasks - TP Summary =
    SUMMARIZE('vw_Tasks',
'vw_Tasks'[TaskPosition],
    'vw_Tasks'[TaskID],
    "No of Tasks", COUNT('vw_Tasks'[TaskPosition]),
"Task (x)", vw_Tasks[TaskPosition] & " ("& COUNT('vw_Tasks'[TaskPosition])&")")
 
Results:
 

What I get vs what I am trying to get:

 

Any help appreciated. 

Need Help

4 Replies

  • selimovd's avatar
    selimovd
    Most Valuable Professional
    4 years ago

    Hey ElliotK ,

     

    in general tables are static, so they won't change after they created. So if in every row the value is "In Progress (1)" or "Not Started (1)", then also the slicer will show this values.

    If you want to change that, you already have to count the number in the summarize table.

     

    By the way calculations within SUMMARIZE can lead to wrong results, so it's best practice to use SUMMARIZE only to summarize the columns and add new columns with ADDCOLUMNS.

     

    The following calculated table should work:

    Tasks - TP Summary = 
ADDCOLUMNS (
    SUMMARIZE ( 'vw_Tasks', 'vw_Tasks'[TaskPosition], 'vw_Tasks'[TaskID] ),
    "No of Tasks",
        -- save the TaskPosition of the current row as Variable
        VAR vTaskPositionCurrentRow = 'vw_Tasks'[TaskPosition]
        RETURN
        -- calculate the count of all TaskPositions of the current status
            CALCULATE (
                COUNT ( 'vw_Tasks'[TaskPosition] ),
                'vw_Tasks'[TaskPosition] = vTaskPositionCurrentRow,
                ALL ( vw_Tasks )
            ),
    "Task (x)",
        -- save the TaskPosition of the current row as Variable
        VAR vTaskPositionCurrentRow = 'vw_Tasks'[TaskPosition]
        RETURN
        -- calculate the count of all TaskPositions of the current status
            vw_Tasks[TaskPosition] & " ("
                & CALCULATE (
                    COUNT ( 'vw_Tasks'[TaskPosition] ),
                    'vw_Tasks'[TaskPosition] = vTaskPositionCurrentRow,
                    ALL ( vw_Tasks )
                ) & ")"
)

     

    If you need any help please let me know.
    If I answered your question I would be happy if you could mark my post as a solution ✔️ and give it a thumbs up 👍

    Best regards
    Denis

    Blog: WhatTheFact.bi
    Follow me: twitter.com/DenSelimovic

    • ElliotK's avatar
      ElliotK
      Helper I
      4 years ago

      Thanks for the sper quick reply. Unfortunately, I get a circular reference when I try to join this make to my existing tables within the model. 

  • luohen's avatar
    luohen
    Resolver II
    4 years ago

    Hi ElliotK ,

    Please update the formula of calculated table [Tasks - TP Summary] as below and check if it can return the correct result...

    Tasks - TP Summary =
    SUMMARIZE (
    'vw_Tasks',
    'vw_Tasks'[TaskPosition],
    'vw_Tasks'[TaskID],
    "No of Tasks", COUNT ( 'vw_Tasks'[TaskPosition] ),
    "Task (x)",
    vw_Tasks[TaskPosition] & " ("
    & CALCULATE (
    COUNT ( 'vw_Tasks'[TaskPosition] ),
    ALLEXCEPT ( 'vw_Tasks', 'vw_Tasks'[TaskPosition] )
    ) & ")"
    )

    If the above one is not working for you, please provide some sample data(exclude sensitive data) with Text format and your expected result with special examples and screenshots. It is better if you can provide me a simplified pbix file. Thank you.

    Best Regards

  • ElliotK's avatar
    ElliotK
    Helper I
    4 years ago

    Thanks for the keen responses everyone. Unfortunately these solutions have not worked. Unfortunately, I have come to the conclusion that this is not possible.