cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sgilmoreBBP
Helper I
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
ToddChitt
Solution Sage
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? 

View solution in original post

10 REPLIES 10
ToddChitt
Solution Sage
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? 

@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 @sgilmoreBBP ,

 

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. 

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

sgilmoreBBP
Helper I
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. 

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

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

sgilmoreBBP_0-1667392134825.png

 

ToddChitt
Solution Sage
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

ToddChitt
Solution Sage
Solution Sage

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

Helpful resources

Announcements
Carousel_PBI_Wave1

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.

Power BI Summit Carousel 2

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!

BizApps LATAM 2023

Business Application LATAM Summit 2023

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

Power Platform Bootcamp

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.