Forum Discussion
KayceVC
Helper II
5 years agoNeed Measure to Partially Ignore Slicer Filter
I am having an issue with a measure where I need it to respect the upper limit based on a slicer, but not the lower. The tables involved are Months, Dates, Projects and Time Entries. Months and D...
- 5 years ago
After working on this off and on for a few days and doing a fair bit of testing, I was able to come with a solution that works. Thank you to everyone who offered suggestions. Working measure is below.
test4 = VAR MaxDate = MAX(Dates[Date]) VAR MinDate = MIN(Dates[Date]) Var MaxTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], ALL(Dates)) Var PeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(Dates, Dates[Date] <= MaxDate && Dates[Date] >= MinDate)) Var ThroughPeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(ALL(Dates), Dates[Date] <= MaxDate)) Var SincePeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(ALL(Dates), Dates[Date] > MaxDate)) Var BeforePeriodTime = CALCULATE(SUM('Time Entries'[Billable_Amt]),'Time Entries'[Included In Billable Hours], FILTER(ALL(Dates), Dates[Date] < MinDate)) VAR BillingAmount = SUM(Projects[Billing_Amount]) RETURN IF(ThroughPeriodTime < SUM(Projects[Billing_Amount]) && (HASONEFILTER(Dates[Week]) || HASONEFILTER(Dates[Month])), PeriodTime, IF(BeforePeriodTime < SUM(Projects[Billing_Amount]) && (HASONEFILTER(Dates[Week]) || HASONEFILTER(Dates[Month])), BillingAmount-BeforePeriodTime, IF(ThroughPeriodTime < SUM(Projects[Billing_Amount]), PeriodTime, IF(BeforePeriodTime < SUM(Projects[Billing_Amount]), BillingAmount-BeforePeriodTime, IF(BeforePeriodTime > SUM(Projects[Billing_Amount]) && (HASONEFILTER(Dates[Week]) || HASONEFILTER(Dates[Month])), 0, IF(BeforePeriodTime > SUM(Projects[Billing_Amount]), BillingAmount,0 ) ) ) ) ) )
lbendlin
Super User
5 years agoCan you confirm that your slicer is fed by an unconnected table?
KayceVC
Helper II
5 years agoThe slicers causing me issues are the Dates and Months slicers. Both slicers are controlled by their own tables with the Months having a relationship with Dates and Dates having a relationship with the Time Entries.