Forum Discussion

ImJustConfused's avatar
ImJustConfused
Frequent Visitor
8 years ago

Creating a dynamic calculated column via slicer

I'm trying to create multiple slicers to filter my data. The first slicer is a date slicer. The second slicer is a count slicer. I have a categorical field, a binary field, and a date field. Similar to below. 

name | isOld  | Date
Bob  | 1      | 1/1/16
Sara | 0      | 9/15/16
Jess | 0      | 2/4/17
Bob  | 1      | 7/70/17
Mike | 1      | 12/17/17
Jess | 1      | 2/28/18
Sara | 0      | 3/23/18

I have a calculated column that counts each name where the isOld field is 1. This column is set to the second slice.r I would like for the calculated column to update with the date field. In this example, if the date slicer was set to all dates after 12/31/16, the maximum for the count slicer would show 1. 

 

Currently, my code for the calculated column is:

NameCount = 
CALCULATE(
    COUNT('Table'[Name]),
    'Table'[isOld] = 1,
    ALLEXCEPT('Table','Table'[Name]))

and it does not update with the slicer. 

3 Replies

  • Mareias's avatar
    Mareias
    Frequent Visitor
    8 years ago

    Hello,

     

    I had the same problem today. The calculated column cant be dynamic.

     

    1. Calculation column/table not support dynamic changed based on filter or slicer. (So sum(table[column]) will return fixed value)
    2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.

     

    Try using a calculated measure and see if that suits you.

    • ImJustConfused's avatar
      ImJustConfused
      Frequent Visitor
      8 years ago

      Mareias Thank you for the suggestion. 

       

      I've tried using a calculated Measure, but I am unable to use that Measure in a slicer. Is there a different way that would help me achieve the same result?

      • v-yulgu-msft's avatar
        v-yulgu-msft
        Microsoft Employee
        8 years ago

        Hi ImJustConfused,

         

        You should manually create an extra table which is unrelated to fact data table to list all available count numbers for users to choose from.

         

        Create measures like below:

        NameCount = 
CALCULATE(
    COUNT('Test Table'[name]),
    'Test Table'[isOld] = 1,ALLSELECTED('Test Table'[Date]))+0

selected CountNo = SELECTEDVALUE(slicertable[CountNo])

check_ = IF([NameCount]=[selected CountNo],1,0)

        Add measure [check_] to visual level filter and set its value to 1.

         

        Best regards,

        Yuliana Gu