Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
Hi,
I am working on a project. The objective of the project is
1. To find the number of store visited in last 3 months. For example. If the user select March -2024, then it will be unique store visited in March -2024, Feb -2024 and Jan -2024. Similary, if the user select Feb -2024 then I need to find the store visited in Feb-2024, Jan-2024 and Dec -2023
2. Find the total store that has Brand BBB (or any other brand or product) in the latest visit. I want to have Brand slicer, product slicer. The user will select the value from this slicer and dax will output out of total store visited in last 3 months how many has those brand.
Data Model,
In the data model,
Product is connect to TRAX SOS by product key
Canvas is connected to TRAX SOS by Store key
Date is connectd to Canvas by Date[Date]
Date is connect to TRAX SOS by Date[Date] . this is inactive relationship.
My logic
When useer chooe date in the slicer (say March 2024), the it filter the canvas to show only the store present in March 2024. This Canvas also filter the TRAX SOS to show only March 2024. But I have removed the fitler from Canvas to TRAX SOS. With this I find teh total store visited in last 3 months as follows
Solved! Go to Solution.
Hi @Mkrishna ,
Please update the formula of variable STORE as below:
VAR STORE =
CALCULATE (
COUNTROWS (
FILTER (
ALL ( 'TRAX SOS' ),
'TRAX SOS'[store_number] = MAX ( 'Canvas'[Store Number] )
&& 'TRAX SOS'[Visit Date] = MaxDate
&& 'TRAX SOS'[Visit Date] >= DATE ( 2023, 12, 01 )
&& 'TRAX SOS'[Visit Date] <= DATE ( 2024, 02, 29 )
&& 'TRAX SOS'[Brand] IN ALLSELECTED ( 'Product'[Brand] )
)
),
USERELATIONSHIP ( 'TRAX SOS'[Visit Date], 'Date'[Date] )
)
Best Regards
Hi @Mkrishna ,
Please update the formula of variable STORE as below:
VAR STORE =
CALCULATE (
COUNTROWS (
FILTER (
ALL ( 'TRAX SOS' ),
'TRAX SOS'[store_number] = MAX ( 'Canvas'[Store Number] )
&& 'TRAX SOS'[Visit Date] = MaxDate
&& 'TRAX SOS'[Visit Date] >= DATE ( 2023, 12, 01 )
&& 'TRAX SOS'[Visit Date] <= DATE ( 2024, 02, 29 )
&& 'TRAX SOS'[Brand] IN ALLSELECTED ( 'Product'[Brand] )
)
),
USERELATIONSHIP ( 'TRAX SOS'[Visit Date], 'Date'[Date] )
)
Best Regards
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
94 | |
92 | |
83 | |
71 | |
49 |
User | Count |
---|---|
143 | |
120 | |
110 | |
59 | |
57 |