Forum Discussion

JihyeHeo's avatar
JihyeHeo
Frequent Visitor
6 years ago

Problem With Applying Page Filter (Date relationship issue)

I try applying a filter in the filter pane to the report to see the values for 2019. However, there is a difference between the filtered value and the actual one.    There are three tables as below...
Need Help
JihyeHeo's avatar
JihyeHeo
Frequent Visitor
6 years ago

Thank you for your reply. However, the problem is not solved...

 

I compared the filtered 2019 value of a total new customer count YTD with the calculated 2019 YTD value.

 

Please refer to the below measures.

 

1. The filtered 2019 value of a total new customer count YTD by selecting 2019 on the filter field.

New Customer Count YTD = CALCULATE ( DISTINCTCOUNT ( 'Table B'[FullCustNo] ), FILTER ( ALLSELECTED ( 'Table B' ), 'Table B'[MNC Year] <= YEAR ( TODAY () ) && 'Table B'[MNC Month] <= MONTH ( TODAY () ) ) )

 

2. Calculated 2019 YTD value:

Last year New Customer Count YTD = calculate(DISTINCTCOUNT('Table B'[FullCustNo]),'Table B'[MNC Year]=YEAR(TODAY())-1,'Table B'[MNC Month]<MONTH(TODAY()))

 

Do you know why both values is not same?

 

v-yingjl's avatar
v-yingjl
Community Support
6 years ago

Hi JihyeHeo ,

It is related with allselected() function. 

The ALLSELECTED function gets the context that represents all rows and columns in the query, while keeping explicit filters and contexts other than row and column filters. This function can be used to obtain visual totals in queries.

If I do not use allselected(), when I set MNC year as 2019 in filter on all pages, it will get 4 not 3 in my sample:

Please refer this document: allselected() function 

 

Best Regards,
Yingjie Li

If this post helps then please consider Accept it as the solution to help the other members find it more quickly.