Forum Discussion
Calculation Help need to pick your brain
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.
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,
KellyDid I answer your question? Mark my post as a solution!
2 Replies
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,
KellyDid I answer your question? Mark my post as a solution!