Crossfilter not working as excpected

[Anonymous]

Hello,

I am trying to calculate new subscribers and leavers.

It seems simple, but it doesn't work.

Here are the DAX measures:

Count Join =
COUNT ( Subscribers[Registration_ID] )

Count Leavers =
CALCULATE (
COUNT ( Subscribers[DateLeft] ),
CROSSFILTER ( 'Calendar'[Date], Subscribers[DateLeft], ONEWAY )
)

Here is the PBIX file:

https://basintamir-my.sharepoint.com/:u:/g/personal/tamir_tamirbasin_com/EXjrPKCMhP5LpwEJzQ1VMpsBpgG...

Thanks for any help,

Tamir

[v-yulgu-msft]

Hi @Anonymous,

I was not very clear about your problem. You said "it doesn't work", while what is the desired result? Could you please illustrate the scenario with less data to help me better understand?

Regards,

Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft

There is a list of people who joined at a certain date (Joineddate).

Some left  (Leftdate).

In order to use the calendar table, in the data model, I create a link between the dimCalendar[date] to the [JoinedDate].

However, to measure the correct number of leavers I need to link the [LeftDate] instead to the dimCalendar[date].

For that reason, I created an inactive connection in the data model and I am trying to activate it through the Crossfilter function for that purpose only (count leavers by month).

Please see the linked file:

MonthlySubscriberCountbysummarizeCorrect

It shows the correct number of leaves per month.

It is done with a different method but I am trying to reproduce the result using the crossfilter function.

I hope it is clearer now,

Thank you for your time

Regards

Tamir

[magsod · Accepted solution]

Hi!

Instead of the CROSSFILTER function you should use the USERELATIONSHIP function to activate your inactive relationship.

Br,

Magnus

[vinaypugalia]

This article will help to futher understand why USERELATIONSHIP() is better than CROSSFILTER()
https://www.inkeysolutions.com/blogs/power-bi-userelationship-vs-crossfilter/

[Anonymous]

@magsod

Thank you.

Works like a charm 🙂

Regards

Tamir