cancel
Showing results 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

Helper III

DAX for If with Filter

Hello

I am trying to create a calculated column that returns a yes or no based on the following conditions.

The max value in column A (LP Actuals'[Last Quantity Entered in past 15 days]) is 1 and also max value in column B ([Last Hours Entered in past 15 days]) is also 1 for that particular job id.

The dataset is a little complicated in the sense that on any particular row, we will not have value for column a and b at the same time. So essentially we need to go through all rows for that particular job and if there is a max value of 1 in column A and max value of 1 in column B, then it must return yes else no

IF(MAX('LP Actuals','LP Actuals'[Last Quantity Entered in past 15 days]=1 && MAX('LP Actuals','LP Actuals'[Last Hours Entered in past 15 days]=1) && 'LP Actuals'[Job Code]=EARLIER('LP Actuals'[Job Code]),"yes","no"))))

1 ACCEPTED SOLUTION
Employee

I think this might be closer (and maybe easier to read)

```New Ccolumn =
VAR Col1 = CALCULATE(MAX('LP Actuals'[Last Quantity Entered in past 15 days]),'LP Actuals'[Job Code] = EARLIER('LP Actuals'[Job Code]))
VAR Col2 = CALCULATE(MAX('LP Actuals'[Last Hours Entered in past 15 days]),'LP Actuals'[Job Code] = EARLIER('LP Actuals'[Job Code]))
RETURN IF(
Col1=1 && Col2=1 ,
--THEN--
"Yes",
--ELSE--
"No"
)```

Proud to be a Datanaut!

3 REPLIES 3
Employee

I think this might be closer (and maybe easier to read)

```New Ccolumn =
VAR Col1 = CALCULATE(MAX('LP Actuals'[Last Quantity Entered in past 15 days]),'LP Actuals'[Job Code] = EARLIER('LP Actuals'[Job Code]))
VAR Col2 = CALCULATE(MAX('LP Actuals'[Last Hours Entered in past 15 days]),'LP Actuals'[Job Code] = EARLIER('LP Actuals'[Job Code]))
RETURN IF(
Col1=1 && Col2=1 ,
--THEN--
"Yes",
--ELSE--
"No"
)```

Proud to be a Datanaut!

Helper III

Thanks Phil

Works well

Helper III

Phil

Thanks for your help. I am experiencing an unusal situation. If I add any calculated column, after the one you helped me to create it says A circular dependency was detected

Can you make a guess on what might be happening

Announcements

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Monthly Update - June 2024

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

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors
Top Kudoed Authors