Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Slicers on calculated columns

Hi all,   I'm feeling a particular frustration with one of my visualisations.   I have a data table which looks something like   Date Type Count 01/09/2020 A 1 01/09/2020 B 2 ...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Anonymous ,

     

    Would you please try to use the following measure:

     

     

    Measure =
VAR A =
    MAX ( 'Date'[Date] )
RETURN
    CALCULATE (
        SUM ( 'Plant Forecasts'[Selection_Count] ),
        ALLSELECTED ( 'Plant Forecasts'[Plant Type] ),
        'Plant Forecasts'[StartDate] <= A,
        'Plant Forecasts'[EndDate] > A,
        'Plant Forecasts'[StageCode_Name] <> "Won"
    )

     

     

     

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

     

    Best Regards,

    Dedmon Dai

edhans's avatar
edhans
Icon for Community Champion rankCommunity Champion
6 years ago

Anonymous can you be a bit clearer on what you've done? Calculated column values will NOT update. They are calculated one time at data refresh, so when you say "What I need to do is to have a slicer on the chart page which adjusts the calulation based on its selection," I'm not clear on what you are trying to do. Perhaps if you shared a PBIX file via onedrive, dropbox, etc. with a clearer direction on expected results? Even a mock up in Excel would be fine.

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks edhans.

 

I fear my problem will be exactly as you say that the calculated column only calulates on refresh but hopefully my excel mock up should make my question clearer. How do I get that to you? I can't see an option to attach the file and my network blocks dropbox and other sharing sites.

 

I have a table (A1:F6) that has data about when a certain number of 'things' have been selected to be on a building site. From that I calculate the table in colums I:J, creating a chart as shown.

 

What I then need to be able to do is be able to filter this by either Type 1 or Type 2, redrawing the chart based on the selection from a slicer, the slicer is fed from the tables A and B also shown in the excel.

 

Can you suggest how I might do this? a complication is that the contents of tables A nd B are not locked and rows can be added over time.

 

Thank you,

 

Ed

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

    Hi Anonymous - Do you have ONeDrive, DropBox, etc? Just paste a share link. If you do not want to paste the link here publically, you can DM it to me.

    If you cannot share at all, can you email it to me? Contact me via DM for that info.

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

    Hi Anonymous ,

     

    Calculated column will not effected by slicer, so you need to create a measure for it:

     

    Measure = IF(MAX('Table 2'[Date])in CALCULATETABLE(VALUES('Table'[Date]),ALL('Table')),CALCULATE(SUM('Table'[Count]),ALLSELECTED('Table'[Type]))+0,BLANK())

     

     

     

    For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZUHCU4XqYRErMkvpeIyVFgBOra79Gt1ArrZnuga29d3Qg?e=zeMwu6

     

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

     

    Best Regards,

    Dedmon Dai

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Dedmon,

      Thank you, this nearly works but not quite. Swapping out the CALCULATE syntax for my own that sums as I need forces me to use a MAX on the date meaning it allows me to plot the value on a day when a measure chages but doesn'tfill in the gaps between when the measure stays constant so I get as attached. What I really need is for each day to plat its total, not just the dates that a measure starts. This would show as every day in between the columns shown as being the same as previous, until a difference in sum occurs.

      I hope this makes sense.

      The good news is that the slicers DO work.

       

       

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

        Hi Anonymous ,

         

        I don't get you exactly , maybe you can give some sample data and expected output for reference.

         

        Best Regards,

        Dedmon Dai