Forum Discussion
IF AND SUMIF OR
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.
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" )
Hi,
Your question is not clear. What exact result do you want?
Ashish_Mathur What I want is disticnt coung of ID
Hi,
Write this measure
=Distinctcount(Data[ID])
Hope this helps.
That was the measure I created but the total was not adding up
Anonymous - What you have is a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376
Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907
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:
Visual sample when Attended is selected in the slicers show 2 for some orga and zero when they should be 1
Visual sample when cancelled is selected in the slicer and returning zeros but I number of cancelled sessions
Please let me know if you require more information Ashish_Mathur
Hi,
I am still not clear. Someone else will help you.