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
chaz2jerry
Advocate III
Advocate III

Track opportunity pipeline change over time with slicer and filter

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

 

1 ACCEPTED SOLUTION
chaz2jerry
Advocate III
Advocate III

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

 

View solution in original post

1 REPLY 1
chaz2jerry
Advocate III
Advocate III

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

 

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.