cancel
Showing results for
Did you mean:
Helper I

## returning true for multiple criteria in calculated column

I'm trying to create a calculated column that shows true if the criteria is met.
The criteria is that if the current day offset matches what I search then it returns true. I'm looking for the the past 4 days that are the same day of the week as today. Using Current day offset allows this to be dynamic.

I am new so please go easy on me. the below is returning the result "#ERROR"

``````is past 4 = IF('Date'[CurrDayOffset] =-6,
&& ('Date'[CurrDayOffset] = -13
&& ('Date'[CurrDayOffset] = -20
&& ('Date'[CurrDayOffset] = -27
THEN  "True"
else  "False"
End ``````

1 ACCEPTED SOLUTION
Solution Sage

is past 4 = IF('Date'[CurrDayOffset] =-6
|| 'Date'[CurrDayOffset] = -13
|| 'Date'[CurrDayOffset] = -20
|| 'Date'[CurrDayOffset] = -27
, "True"
, "False"
)

The IF statement in DAX is pretty ubiquitous. Learn it.

What is the logic you are trying to accomplish here?

10 REPLIES 10
Solution Sage

is past 4 = IF('Date'[CurrDayOffset] =-6
|| 'Date'[CurrDayOffset] = -13
|| 'Date'[CurrDayOffset] = -20
|| 'Date'[CurrDayOffset] = -27
, "True"
, "False"
)

The IF statement in DAX is pretty ubiquitous. Learn it.

What is the logic you are trying to accomplish here?

Helper I

@ToddChitt I understand the IF Statement from excel. I've discovered that with some functionalty, the cross over from excel to PBI is not always the same hence why I was clutching at straws trying to manipulate the IF statement to make this work.

My overall goal is from my table named " Production Stats" use a column named "Daily total Packs Per Hr" to find the the value for the same day as today but for the previous four weeks (not including today) and average this value out. The reason I was looking to use current day offset is becuase I want it to update every day to show the past 4 Mondays, or past 4 Tuesdays. All relevant to the current day of the week.

 30.09.22 355 328 4501 03.10.22 325 234 4241 04.10.22 323 215 4195 05.10.22 313 268 4072 06.10.22 0 0 0 07.10.22 0 0 0 10.10.22 267 417 3604 11.10.22 326 212 4195 12.10.22 319 264 3954 13.10.22 308 247 4281 14.10.22 0 4120 17.10.22 315 459 3783 18.10.22 332 423 4054 19.10.22 323 557 3882 20.10.22 340 1232 4504 21.10.22 357 579 3742 24.10.22 358 608 4073 25.10.22 376 532 4178 26.10.22 364 685 3913 27.10.22 353 537 3908 28.10.22 195 301 3257 31.10.22 324 3430 01.11.22 02.11.22

This final value will be returned on a card.

I've attached some screenshots and excel data

Community Support

Hi @sgilmoreBBP ,

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 calculated column.

``````Column = var _max_date = MAX('Table'[Date])

var _curent_date = 'Table'[Date]

return

if(_curent_date in {_max_date-7,_max_date-14,_max_date-21,_max_date-28},1,0)``````

(3) We can create  a measure.

``````average = var _a = CALCULATE(SUM('Table'[Daily total Packs Per Hr(Line 2)]),FILTER(ALL('Table'),'Table'[Column]=1))
var _b=CALCULATE(SUM('Table'[Column]),FILTER(ALL('Table'),'Table'[Column]=1))
var _c = DIVIDE(_a,_b,blank())
return _c``````

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

Helper I

Thank you for your detailed response much appreciated. ToddChitt's solution did exactly was I was looking for.

Solution Sage

If you would, please mark the answer as an aAcepted Solution, that way others investigating similar issues can know this has a good answer. Also helps us responders 'level up' :).

Helper I

Hi @ToddChitt both suggestions return a syntax error. I appreciate you taking the time to reply.

there may be a completely different way to achieve this I'm just to new to power BI to know if there is.

Solution Sage

Sorry, replace END with )

Syntax for IF statement in DAX is:

IF ( <expression to evaluate to either TRUE or FALSE>, <Value to return if TRUE>, <Value to return if FALSE> )

Helper I

@ToddChitt I tried that previous to you replying. It auto corrected the singular ")" to "))))" then returned an error.

Solution Sage

But the above will ALWAYS return FALSE because Date'[CurrDayOffset] cannot be BOTH a value of -6 AND a value of -13 at te same time. Try some OR logic by substituting double pipes for the double &&:

is past 4 = IF('Date'[CurrDayOffset] =-6
|| ('Date'[CurrDayOffset] = -13
|| ('Date'[CurrDayOffset] = -20
|| ('Date'[CurrDayOffset] = -27
, "True"
, "False"
End

Solution Sage

is past 4 = IF('Date'[CurrDayOffset] =-6
&& ('Date'[CurrDayOffset] = -13
&& ('Date'[CurrDayOffset] = -20
&& ('Date'[CurrDayOffset] = -27
, "True"
, "False"
End

Announcements

#### 2023 Release Wave 1 Plans

Power BI release plans for 2023 release wave 1 describes all new features releasing from April 2023 through September 2023.

#### Global Power BI Training

Make sure you register today for the Power BI Summit 2023. Don't miss all of the great sessions and speakers!

#### Business Application LATAM Summit 2023

Join the biggest FREE Business Applications Event in LATAM this February.

#### Global Power Platform Bootcamp

In this bootcamp we will deep-dive into Microsoft’s Power Platform stack with hands-on sessions and labs, delivered to you by experts and community leaders.

Top Solution Authors
Top Kudoed Authors