topic Re: Create a Flag measure based on Start date and End Date. in DAX Commands and Tips

Create a Flag measure based on Start date and End Date.

Anonymous — Thu, 06 Apr 2023 16:49:05 GMT

Hello All,

I have a table and I need to show records between start date and End date.

Need to create a flag which has to be 1 for dates which is between start date and End Date. So that I can drop flag in measure and select "1" to show the records.

Below is the Dax I used but gettting error "Multiple Values supplied instead of one"

Flag =

Var startdate=SELECTEDVALUE('Selection Values'[CAL_DT])

var Startdate_plus = startdate+1

Var PStrt_date= startdate-365

var enddate= startdate+14
return IF(AND(VALUES(V_DAILY_SERVICE_CALL_LOG[CAL_DT]) >= IF(VALUES(V_DAILY_SERVICE_CALL_LOG[STATUS_ID]) IN {"A501","A405","A411"},PStrt_date,Startdate_plus),VALUES(V_DAILY_SERVICE_CALL_LOG[CAL_DT]) <= enddate),1,0

Here (V_DAILY_SERVICE_CALL_LOG[CAL_DT] is retireved by using lookup from date table.

Below is the model:

My fact table is V_DAILY_SERVICE_CALL_LOG. Date slicer is coming from a separate table which is not in relation with Fact table and Service dest is coming from a Dimension table which is in One to Many relation with Fact.

Relative SQL query for flag is below, I want to convert that Flag query to Flag measure so that I can drop this to Table Vizualization filter pane:-

Select * from(
Select f.*,
CASE When d.CAL_DT BETWEEN (CASE WHEN STATUS_ID IN ('A501','A405','A411') THEN DATEADD(DAY,-365,'2023-03-04') ELSE DATEADD(DAY,1,'2023-03-04') END) AND DATEADD(DAY,14,'2023-03-04') THEN 1 ELSE 0 END as Flag
from "LESL_DW_UAT"."PBI_MART"."V_DAILY_SERVICE_CALL_LOG" f
inner join "LESL_DW_UAT"."PBI_MART"."V_DATE_DIM" d on f.DT_SKEY = d.DT_SKEY
inner join "LESL_DW_UAT"."PBI_MART"."V_STORE_SERVICE_DIM" s on f.STORE_KEY = s.STORE_KEY
where
s.SERVICE_DSTRCT_CD ='22' ) A where Flag = 1

Re: Create a Flag measure based on Start date and End Date.

Wilson_ — Sat, 08 Apr 2023 13:29:51 GMT

Hello,

Any chance you can share your pbix (or at least a smaller sample version)?

Re: Create a Flag measure based on Start date and End Date.

Anonymous — Wed, 12 Apr 2023 05:59:55 GMT

Hi Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data.

(2) We can create a measure.

Flag = var _startdate=CALCULATE(MAX('Selection Values'[CAL_DT]),FILTER(ALL('Selection Values'),'Selection Values'[STATUS_ID]=MAX('V_DAILY_SERVICE_CALL_LOG'[STATUS_ID]))) var _enddate=_startdate+14 var _flag=IF(SELECTEDVALUE('V_DAILY_SERVICE_CALL_LOG'[CAL_DT])>=_startdate && SELECTEDVALUE(V_DAILY_SERVICE_CALL_LOG[CAL_DT])<=_enddate,1,0) return _flag

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

Re: Create a Flag measure based on Start date and End Date.

Anonymous — Wed, 12 Apr 2023 07:06:57 GMT

Anonymous ,

Thanks for the response.
In my case, There are two conditions

date between status_id in ('A501','A405','A411') then start_date -365 else start_date+1 and End_date.

I added a sample pbix. please download pbix with sample data.

https://drive.google.com/file/d/1l17xTVn0iPwSZfB_mODdcljxKwf0dAVY/view?usp=share_link

Re: Create a Flag measure based on Start date and End Date.

Anonymous — Wed, 12 Apr 2023 07:07:33 GMT

Hi Wilson_ ,

https://drive.google.com/file/d/1l17xTVn0iPwSZfB_mODdcljxKwf0dAVY/view?usp=share_link

Please find the attched pbix

Re: Create a Flag measure based on Start date and End Date.

Anonymous — Wed, 12 Apr 2023 07:57:18 GMT

Hi Anonymous ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure.

Flag = Var startdate=SELECTEDVALUE('Selection Values'[CAL_DT]) var Startdate_plus = startdate+1 Var PStrt_date= startdate-365 var enddate= startdate+14 var _sdate=IF(MAX('Fact'[STATUS_ID]) in {"A501","A405","A411"},PStrt_date,Startdate_plus) return IF(MAX('Fact'[CAL_DT])>=_sdate && MAX('Fact'[CAL_DT])<=enddate,1,0)

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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