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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
ToddChitt
Super User
Super User

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? 

View solution in original post

10 REPLIES 10
ToddChitt
Super User
Super User

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? 

Anonymous
Not applicable

@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.223553284501
03.10.223252344241
04.10.223232154195
05.10.223132684072
06.10.22000
07.10.22000
10.10.222674173604
11.10.223262124195
12.10.223192643954
13.10.223082474281
14.10.22 04120
17.10.223154593783
18.10.223324234054
19.10.223235573882
20.10.2234012324504
21.10.223575793742
24.10.223586084073
25.10.223765324178
26.10.223646853913
27.10.223535373908
28.10.221953013257
31.10.22 3243430
01.11.22   
02.11.22   



This final value will be returned on a card. 

I've attached some screenshots and excel data


sgilmoreBBP_0-1667394499406.png

 



Hi @Anonymous ,

 

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

(1) This is my test data.  

vtangjiemsft_0-1667893749705.png

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

vtangjiemsft_3-1667894285604.png

 

vtangjiemsft_2-1667894199033.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. 

Anonymous
Not applicable

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

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' :).

Anonymous
Not applicable

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. 

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> )

Anonymous
Not applicable

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

sgilmoreBBP_0-1667392134825.png

 

ToddChitt
Super User
Super User

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

ToddChitt
Super User
Super User

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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