Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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 |
01/09/2020 | C | 3 |
02/09/2020 | A | 1 |
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....
Date | Sum |
01/09/2020 | 6 |
02/09/2020 | 1 |
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....
Date | Sum |
01/09/2020 | 2 |
02/09/2020 | 0 |
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
Solved! Go to 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
@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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI ReportingThanks 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())
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.
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
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.
DAX is for Analysis. Power Query is for Data Modeling
Proud to be a Super User!
MCSA: BI Reporting@ejoneslor , not clear. refer if these can help
https://radacad.com/change-the-column-or-measure-value-in-a-power-bi-visual-by-selection-of-the-slic...
https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...
https://www.youtube.com/watch?v=vlnx7QUVYME
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
64 |
User | Count |
---|---|
135 | |
120 | |
101 | |
71 | |
61 |