topic Re: Issues with CrossFilter in DAX Commands and Tips

Issues with CrossFilter

Anonymous — Fri, 18 Sep 2020 07:52:45 GMT

Hi,

I have 2 columns Accounting and Date joined via Both side filter in model. For a measure, I am using crossfilter to override this join as Single side -

My crossfilter on Accounting and Date table works as expected, when I run the following DAX (i.e. returns values for dates which might not have accounting entries):

Cumulative Amount=

var total = CALCULATE([Amount],CROSSFILTER('Date'[Date],Accounting[Effective Date],OneWay),ALLSELECTED('Accounting'))

return total

But the results are not as expected when I add an additional filter on date (i.e. it does not return values for the dates where there are no entries in accounting:

Cumulative Amount=

var total = CALCULATE([Amount],CROSSFILTER('Date'[Date],Accounting[Effective Date],OneWay),FILTER(all('Date'),'Date'[Date]<=max('Date'[Date])),ALLSELECTED('Accounting'))

return total

Is that a limitation or is there a workaround?

Re: Issues with CrossFilter

amitchandak — Fri, 18 Sep 2020 08:59:19 GMT

Anonymous , Looking at this I am not sure on the need of ALLSELECTED('Accounting')

remove that and try. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Re: Issues with CrossFilter

Anonymous — Mon, 21 Sep 2020 05:28:44 GMT

amitchandak , I am unable to share any file outside my org network.

The ALLSELECTED('Accounting') was used from my original which is slightly more complex. But even after removing that there is no change in output:

Amount (As on date) - INR =

var total = CALCULATE([Amount (For the period) - INR],CROSSFILTER('Date'[Date],Accounting[Effective Date],OneWay),FILTER(all('Date'),'Date'[Date]<=max('Date'[Date])))

return total

Following is the output when I use both side filter b/n Accounting and Date:

Following is the expected/required output achieved with single side filter b/n Accounting and date:

But I need to keep the Filter b/n Accounting and Date to Both side for some other use case, hence was tring to override the filter just for this measure. But its not working. Any idea, where its going wrong?

Thanks,

Shailee