Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mkrishna
Helper III
Helper III

Removing existing date filter before applying new date filter---DAX

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,

Mkrishna_1-1714471577330.png


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 

 

Common Stores =

 

VAR StartDate = STARTOFMONTH(DATEADD('Date'[Date], -2, MONTH))
VAR FinalDate = MAX('Date'[Date])

 

VAR Customers = CALCULATETABLE(VALUES('Canvas'[Store Number]))
VAR PriorCustomers = CALCULATETABLE(
    VALUES('TRAX SOS'[store_number]),
    FILTER(
        ALL('Date'),
        'Date'[Date] >= StartDate &&
        'Date'[Date] <= FinalDate
    ), ALL('Product'), ALL(Canvas), USERELATIONSHIP('TRAX SOS'[Visit Date], 'Date'[Date]))



VAR CountCommonCustomers = COUNTROWS(INTERSECT(Customers, PriorCustomers))

 

VAR Result =
    if(ISBLANK(CountCommonCustomers), 0, CountCommonCustomers)

 

RETURN
Result

Common Store is correct as per my need.
 
Now I find, how many store within this common store is visited has brand BB. I want the Brand to be in slicer and not encoded in code.
No of Store 2 =
VAR ReferenceDate = MAX('Date'[Date])

VAR PreviousDate =
    DATESINPERIOD('Date'[Date], ReferenceDate, -3, MONTH)

VAR MaxDate =
    MAXX (
        FILTER (
            ALL ( 'TRAX SOS' ),
            'TRAX SOS'[store_number] = MAX ( 'Canvas'[Store Number] ) &&
            'TRAX SOS'[Visit Date] IN PreviousDate
        ),
        'TRAX SOS'[Visit Date]
    )

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)
            )
        ),
        USERELATIONSHIP('TRAX SOS'[Visit Date], 'Date'[Date])
    )

RETURN
    IF ( STORE > 0, 1, 0 )
 
There is problem in second code call Nos of Store 2 especiall in VAR Store calculation. I have used ALL (TRAX SOS ) on the VAR Store calculation. Using ALL gives me the desired result but then it make other slicer coming from Proudct non efffective i.e the result is not affect by slicer. If I do not use ALL in VAR STore calculation, I get only result for 1 month (i.e. for the month selected. I have hardcoded the momnth in the VAR STore calculation for Date rang JAn 2024 to March 2024. I can change this but I am not able to find the replacement for ALL. If I do not use ALL, the date filter coming from Canvas also filter the TRAX SOS and the VAR STORE is calculated for only 1 month. 

REquest for help
1 ACCEPTED SOLUTION
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-yiruan-msft
Community Support
Community Support

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

Community Support Team _ Rena
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.