Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Issues with CrossFilter

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?
crossfilter

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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