Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi everyone, I am trying to track the change of salesforce opportunities over time. I have followed the thread below to get the logic working (added a calculated column in the reference table table to show the open opportunities as of each date), and put it on a time chart to show progression over time (with the data table as x-axis, open opportunity amount as y-axis).
But I cannot seem to apply existing slicers onto this chart. For example, I have salespersons info in the opportunity table, and added a slicer to filter by salespersons, but this slicer does not affect the time chart. It seems the requester in the following thread was able to solve using Lookupvalue function but I couldn't understand it without details. Any one able to shed some light on this one?
https://community.powerbi.com/t5/Desktop/Creating-trends-or-snapshots-of-moving-data/td-p/8460
EDIT: Following is the calc column formula located in the date dimension table, for your reference. I have a salesperson dimension table that relates to the "DataSFDC_Oppo" fact table and is used to slicer by salesperson. But the slicer doesn't work on any visual using this calc column.
t_OpenFunnel by Oppo =
SUMX (
FILTER (
DataSFDC_Oppo,
( DataSFDC_Oppo[Date_Closed__c] > Ref_DateTable[Date] || DataSFDC_Oppo[Date_Closed__c] = BLANK() )
&& DataSFDC_Oppo[CreatedDate] <= Ref_DateTable[Date]
),
DataSFDC_Oppo[Amount]
)
Solved! Go to Solution.
After some additional research, I found that it's due to the limitation of Calc Columns. I had to use Calc Measure (adjusted formula a bit to reflect the measure) and the slicer is now working as expected. I referenced info in the following post, but I did not have to add the VALUES() function.
https://community.powerbi.com/t5/Desktop/Filtering-records-based-on-a-slicer/td-p/34219
After some additional research, I found that it's due to the limitation of Calc Columns. I had to use Calc Measure (adjusted formula a bit to reflect the measure) and the slicer is now working as expected. I referenced info in the following post, but I did not have to add the VALUES() function.
https://community.powerbi.com/t5/Desktop/Filtering-records-based-on-a-slicer/td-p/34219
User | Count |
---|---|
128 | |
108 | |
100 | |
64 | |
62 |
User | Count |
---|---|
136 | |
113 | |
102 | |
71 | |
60 |