Hello,
I have written a dax code.
My aim is to count all the store number that has value greater than 0 as per the condition specified by the STORE variable. But I am not able to produce the return statement
The DAX Code that I have written is as follows:
Measure =
VAR _Reference_Date = MAX(‘Date’[Date])
VAR _Previous_Dates = DATESINPERIOD(
‘Previous Date’[Date],
_Reference_Date,
-3,
MONTH
)
VAR Customers = VALUES(‘Data’[Store Number])
VAR MaxDate = MAXX(
FILTER(
ALL(‘Main Table’),
‘Main Table’[Store Number] = MAX(‘Data’[Store Number]) &&
‘Main Table’[Visit Date] IN _Previous_Dates
),
‘Main Table’[Visit Date]
)
VAR STORE =
CALCULATE(
COUNTROWS(
FILTER(
‘Main Table’,
‘Main Table’[Store Number] = MAX(‘Data’[Store Number]) &&
‘Main Table’[Visit Date] = MaxDate &&
‘Main Table’[Brand] = “Pepsi”
)
),
REMOVEFILTERS(‘Date’),
KEEPFILTERS(_Previous_Dates),
USERELATIONSHIP(‘Date’[Date], ‘Previous Date’[Date])
)
The return statement that I am using is
RETURN if(store>0,1,0).
But this return statement gives me all the stores in customers. But I only want to count the stores whose STORE >0.
I should get the Total 5 as these many store has Measure >0 but I am getting 7. I am also getting Total as 1 instead of 5. Please do help
