CROSSFILTER not filtering correctly

[blytonpereira]

Hello

Instead of using a BIDIRECTIONAL filter I am trying to use a CROSSFILTER in my formula.

I have 3 TABLES

• SALES
• CONSENSUS
• Key Unique P2

I have a one to many relationship from my Key table to my Sales and CONSENSUS tables.

The End User Sales is present in the SALES table. The Affiliate Sales is present in the CONSENSUS table.

I would like to now use the cross filtering on P2 for each of the tables to display Affiliate Sales and End User Sales by Country (Country column is picked from my CONSENSUS table, it also exists in the SALES table).

The results seem to appear correctly for Affiliate Sales (because the Country column was taken from this table),

However the results for End user sales are not correct (Ideally the Affiliate sales should be very close to End User Sales).

The TOTALS of the columns seem to be appearing correctly however the values on the individual rows are incorrect for the End User Sales

Some help would be appreciated here. I have attached my file here:

https://drive.google.com/open?id=17yXdiizx8hbTaOdLvkq3c-V2w--kkBUz

Thank you

[AlB]

Hi @blytonpereira

I've had a look at your file and I don't think there's anything wrong with the code for your measure [End User Sales (u)]:

End User Sales (u) =
CALCULATE (
    SUM ( 'SALES'[Sum of 2018 YTD Q4 Units] );
    CROSSFILTER ( P2[P2]; CONSENSUS[Planning Hierarchy 2]; BOTH )
)

It looks fine to me and, to make sure, I tried with a different measure that should yield the same. It does:

TestMeasure3 =
CALCULATE (
    SUM ( SALES[Sum of 2018 YTD Q4 Units] );
    TREATAS ( VALUES ( CONSENSUS[Planning Hierarchy 2] ); SALES[SKU] )
)

I believe the problem lies with the columns you are using for the relationships. Let's look at an example:

• We first filter CONSENSUS by [Country Description] = Algeria and extract all the values in CONSENSUS[Planning hierarchy 2] belonging to Algeria.
• Then we apply those values to SALES, which is what you would do with the CROSSFILTER( ).

I would expect those values to belong only to Algeria but in SALES, they appear in a whopping 51 countries (see list below). Algeria does not even appear in the SALES table. Something similar happens to many other countries. Is this what you'd expect?

You could filter using the country names (not sure if that's what you need).

PS-The list of countries for Algeria:

United Kingdom of Great Britain & Northern Ireland, ISRAEL, LITHUANIA, NORWAY, SOUTH AFRICA, SPAIN, SWITZERLAND, PORTUGAL, CZECHIA, Netherlands (the), Russian Federation (the), LATVIA, SWEDEN, AUSTRIA, FINLAND, GREECE, TURKEY, SLOVAKIA, CROATIA, BELGIUM, DENMARK, NEW ZEALAND, HONG KONG, MALAYSIA, JAPAN, Korea (the Republic of), ECUADOR, CANADA, United States of America (the), AUSTRALIA, MEXICO, GERMANY, Taiwan (Province of China), COLOMBIA, BRAZIL, CHILE, ESTONIA, FRANCE, IRELAND, ITALY, POLAND, SAUDI ARABIA, SINGAPORE, ARGENTINA, PUERTO RICO, PERU, INDIA, CHINA, SLOVENIA, HUNGARY, ROMANIA