Calculation Help need to pick your brain

[Anonymous]

Hi  I am doing a direct query report.  I have a Store Slicer on the top. 

When you select a store from slicer , i get list of customers for that particular store  lets says 10 customers from our fact table. 

I want to find number of times those 10 customers purchased from all other stores except the selected store for this year and pevious year and forever.

How can i approach this in DAX?

Thanks.  

[v-kelly-msft · Accepted solution]

Hi  @Anonymous ,

You need to first create a slicer table with all distinct values of stores;

Then create a measure as below:

Measure = 
var _selectedcustomers=CALCULATETABLE(VALUES('Table'[Customer ]),FILTER(ALL('Table'),'Table'[Store]=SELECTEDVALUE('Slicer table'[Store])))
Return
COUNTX(FILTER('Table','Table'[Customer ] in _selectedcustomers&&'Table'[Store]<>SELECTEDVALUE('Slicer table'[Store])&&'Table'[Year]=YEAR(TODAY())||'Table'[Year]=YEAR(TODAY())-1),'Table'[Store])

And you will see:

Pls see attachment for details.

Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Anonymous]

Thank you much appreciated. 🙂