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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

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

Hello All,

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

anupaminfo_0-1680798981217.png

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:
anupaminfo_1-1680799711873.png

 

 
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
1 ACCEPTED SOLUTION

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.

vtangjiemsft_0-1681286214827.png

 

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. 

View solution in original post

5 REPLIES 5
Anonymous
Not applicable

@v-tangjie-msft ,

 

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

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.

vtangjiemsft_0-1681286214827.png

 

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. 

v-tangjie-msft
Community Support
Community Support

Hi @Anonymous ,

 

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

(1) This is my test data. 

vtangjiemsft_0-1681279033479.pngvtangjiemsft_1-1681279046082.png

(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.

vtangjiemsft_2-1681279112618.png

 

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. 

Wilson_
Super User
Super User

Hello,

 

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!





Anonymous
Not applicable

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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

Top Kudoed Authors