IF AND SUMIF OR

I have the below formula in excel but I need help to write same formular in DAX =IF(AND(SUMIF(A:A,A2,B:B) >=10,C2="face"),"Eng" ,IF(AND(SUMIF(A:A,A2,B:B) >=20,OR(C2="face",C2="online")),"PS" ,IF...

Need Help

Ashish_Mathur

6 years ago

Hi,

This calculated column formula works

=if(AND(CALCULATE(SUM(Data[hours]),FILTER(Data,Data[Name]=EARLIER(Data[Name])))>=10,Data[method]="face"),"Eng",IF(AND(CALCULATE(SUM(Data[hours]),FILTER(Data,Data[Name]=EARLIER(Data[Name])))>=20,OR(Data[method]="online",Data[method]="face")),"PS",IF(AND(Data[status]="pass",Data[priority]="CS"),"Up","Unknown")))

Hope this helps.

Greg_Deckler

6 years ago

Anonymous - I did find one slight error in Anonymous's formula so I corrected it and did some formatting. PBIX is attached.

Result Output = 
    VAR __Sum = SUMX(FILTER(ALL('Table'),[Name]=EARLIER([Name])),[hours])
RETURN
    SWITCH(
        TRUE(),
        __Sum >= 10 && [method]="face","Eng",
        __Sum >= 20 && ([method]="face" || [method]="online"),"PS",
        [status] = "pass" && [priority] = "CS","Up",
        "Unknown"
    )

PSUpEng.pbix35 KB

Anonymous

Not applicable

6 years ago

Ashish_Mathur

am trying to archive is first attendance occurence. Example below, bronte school attended 3 different sessions but I want to the count of their earliest attendance and still able to see sum of other status when selected in the slicer

Bronte School	NE	North Jam	Primary	02/03/2020	10090	Attended
Bronte School	NE	North Jam	Primary	18/03/2020	10090	Attended
Bronte School	NE	North Jam	Primary	19/03/2020	10070	Attended

See sample data and Result column:

Organisation	Region	SP	Phase	date	attendance	Status	Result Column
Bronte School	NE	North Jam	Primary	02/03/2020	10090	Attended	1
Bronte School	NE	North Jam	Primary	18/03/2020	10090	Attended
Bronte School	NE	North Jam	Primary	19/03/2020	10070	Pending
Ola School	NW	North West	Secondary	19/03/2019	10090	Attended	1
Ola School	NW	North West	Secondary	20/03/2019	10090	Attended
Ola School	NW	North West	Secondary	22/03/2019	10080	Absent
Help College	NE	North West	others	05/08/2020	10090	Attended	0
Help College	NE	North West	others	10/08/2019	10090	Attended	1
Help College	NE	North West	others	01/05/2020	10090	Attended	0
Bronte School	NE	North Jam	Primary	02/03/2020	10091	Cancelled

I have used the formula below but does seems to be giving me what I want, as its returning zero some attended status and 2 count in some occassions .

Result calculated = if(Sheet1[date]=minx(filter(Sheet1,Sheet1[Organisation]=EARLIER(Sheet1[Organisation]) && Sheet1[Status]="Attended"),Sheet1[date]) && Sheet1[Status] = "Attended",1,0)

I also want to be able to return values of other status [Cancelled,Absent,Pending] when select them in my slicer

Visual Sample: