Troubleshoot Measure Context - Tools to view active filters?

[pistachio]

I have a measure which works correctly on one page and incorrectly on another. I would like to know if there is a good way to troubleshoot which filters/contexts etc are active.

The measure in question is:

maxDate = MAX ( DimDate[Date] )

# Loans Open = 
    CALCULATE(
	DISTINCTCOUNT(DimAccount[Pk]), 
		FILTER(
			DimAccount,
			DimAccount[DateOpened] <= [maxDate]
                        && OR(DimAccount[DateClosed] >= [maxDate], DimAccount[status] = 1)
                        && DimAccount[ProductTypeId] <> "D"
			) 

	) 

Page 1 works with a date slider selecting a date range. This produces a line chart of # Loans Open over time. On the 31 March this shows the correct figure.

Page 2 works with a date slicer to select the month. With March selected this produces an incorrect figure (too high).

[v-juanli-msft · Accepted solution]

Hi @pistachio 

amitchandak's suggestions are valuable.

I can reproduce your problem, 

In Pic 2, since there are many years' data, selecting 3 from month slicer means March for all years.

I'm afraid there is no auto tool to troubleshoot for the measure/ slicer problem,

if you have any problem, feel free to ask it here.

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[amitchandak]

Check a couple of things.

1. Is the Month-Year (not only month) coming from the date dimension table and table is marked as date.

2. Try to add a filter to remove any already existing date joining.  like --CROSSFILTER(Employee[Start Date],'Date'[Date],None)

Current Employees = CALCULATE(COUNTx(FILTER(Employee,Employee[Start Date]<=max('Date'[Date]) && (ISBLANK(Employee[End Date]) || Employee[End Date]>max('Date'[Date]))),(Employee[Employee Id ])),CROSSFILTER(Employee[Start Date],'Date'[Date],None))

Refer pbix given in

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-tr...
https://community.powerbi.com/t5/Community-Blog/Power-BI-Working-with-Non-Standard-Time-Periods/ba-p...
https://community.powerbi.com/t5/Community-Blog/Comparing-Data-Across-Date-Ranges/ba-p/823601