Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
Solved! Go to 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.
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.
Thank you much appreciated. 🙂
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
108 | |
96 | |
95 | |
74 | |
71 |