Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn 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 |
---|---|
96 | |
86 | |
78 | |
72 | |
67 |
User | Count |
---|---|
110 | |
104 | |
84 | |
65 | |
63 |