Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ejoneslor
Helper II
Helper II

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

 

DateTypeCount

01/09/2020

A1
01/09/2020B2
01/09/2020C3
02/09/2020A1

 

I then have a calculated column in another date table which provides a sum of the 'Count' column for each date, so based on above it would look like....

 

DateSum
01/09/20206
02/09/20201

 

Great, this works and I have the result flowing into a column chart.

 

What I need to do is to have a slicer on the chart page which adjusts the calulation based on its selection, so, if 'Type' B were selected it would return....

 

DateSum
01/09/20202
02/09/20200

 

I've played with the table relationships until I'm blue in the face but I cannot get it to correctly apply.

 

Is what i'm trying even possible and if so how is it achieved?

 

All help appreciated,

 

Ed

1 ACCEPTED SOLUTION

Hi @ejoneslor ,

 

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

View solution in original post

10 REPLIES 10
edhans
Super User
Super User

@ejoneslor 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

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

Hi @ejoneslor ,

 

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())

 

 

Capture2.PNG

 

For more details, please refer to the pbix file: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EZUHCU4XqYRErMkvpe...

 

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

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.

 

ejoneslor_0-1599559109360.png

 

Hi @ejoneslor ,

 

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

 

Best Regards,

Dedmon Dai

 

Dedmon,

 

I'm happy to provide an example but the usual dropbox/file sharing options aren't available to me as my company blocks them.

 

Essentially this is counting equipment hires so a hire may start on 1st September and be out for say two weeks. This would mean that I would need the chart to show 1 item for 14 days as opposed to now where it shows the one item for the 1 day. I have multiple hires starting on different days and running for different periods. The calculate function that allows me to do that looks like

 

= calculate(sum('Plant Forecasts'[Selection_Count]),filter('Plant Forecasts','Plant Forecasts'[StartDate]<='Date'[Date]),filter('Plant Forecasts','Plant Forecasts'[EndDate]>'Date'[Date]),filter('Plant Forecasts','Plant Forecasts'[StageCode_Name]<>"Won"))
 
Using a measure forces me to apply a MAX to all of my dates meaning I only ever see the counts on a date that a hire starts, not the ongoing count of it between two dates.

Hi @ejoneslor ,

 

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

That seems to have cracked it. Awesome, thank you!

Hi @ejoneslor - 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.



Did I answer your question? Mark my post as a solution!
Did my answers help arrive at a solution? Give it a kudos by clicking the Thumbs Up!

DAX is for Analysis. Power Query is for Data Modeling


Proud to be a Super User!

MCSA: BI Reporting

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.