Forum Discussion

Burak83_'s avatar
Burak83_
Regular Visitor
4 years ago
Solved

COUNTIFS SUMIFS Power Pivot

Hi,

I am quite new ti this forum but what I see this forum is a treasure! I hope I can find an answer to my question as well!

I am trying to solve my problem in power pivot by writing a measure; I have achieved to solve my problem actually using excel function but I want to solve it using dax and measure

In this data, there are years where problem occurred, event descriptions and finally lost hours.
As a rule, to call an event as a cause;

  • Same event should happen 4 times or more in that year. In this case, total lost hours might be less than 8 hours
  • Same event might happen less than 4 times in that year. In this case, total lost hours should be more than 8 hours
  • Cause column is what I try to achive. I have achived by using following excel formula 
  • =IF(COUNTIFS(C:C,'UDT Analysis.xlsx'!#REF!,F:F,'UDT Analysis.xlsx'!#REF!)>3,"Yes",IF(SUMIFS(D:D,C:C,'UDT Analysis.xlsx'!#REF!,F:F,'UDT Analysis.xlsx'!#REF!)>7,"Yes","No"))

I will be so glad if you could help me to create a dax measure look similar to my excel formula. Thanks a lot for help and comments!

PlantDepartmentDescription  Lost hoursLost TonsYearQuarterMonthMonth NameWeekCause
SRIProductionLow Pressure Pump A2,6221202225May21No
SRIProductionBroken Chain Drive B2,1182202225May21No
SRIProductionBroken Chain Drive A0,868202225May20No
SRIProductionBroken Chain Drive B1,5130202225May20No
SRIProductionA1234TT0,974202225May20No
SRIProductionA1234TT0,14202225May20No
SRIProductionB758PT0,642202225May20No
SRIProductionB758PT0,14202225May20No
SRIProductionSDT0,323202225May20No
SRIMaintenanceSDT0,426202225May20No
SRIProductionBroken Roller0,217202225May20No
SRIProductionBroken Pump A2148202225May20No
SRIProductionBroken Pump A0,429202225May20No
SRIProductionGrinder B dwon189202225May20No
SRIProductionDowntime 0,213202225May20No
SRIMaintenanceDowntime 0,19202225May20No
SRIProductionGrinder B dwon03202225May20No
SRIProductionB758PT0,329202225May20No
SRIProductionStart up after downtime0,215202225May20No
SRIProductionLow Temperature Heater A1,4122202225May20No
SRIOtherSdt17,11,265202225May20Yes
SRIProductionLow Temperature Heater A02202225May20No
SRIProductionBroken Roller0,649202225May20No
SRIOtherSdt241,775202225May20Yes
SRIProductionBroken Bolt Pump A03202225May20No
SRIProductionBroken Bolt Pump A1,6138202225May20No
SRIProductionValve problem 0,112202225May20No
SRIOtherValve problem 0,110202225May20No
SRIOtherSDT16,71,234202225May20Yes
SRIMaintenanceBroken Bolt Pump A185202225May20No
SRIProductionA1234TT03202225May20No
SRIProductionTurbine failure0,324202225May20No
SRIProductionStart up after downtime1,191202225May20No
SRIProductionTurbine failure0,763202225May19No
SRIOtherNo power1,195202225May19No
SRIOtherNo power0,978202225May19No

 

PowerBI Countifs
  • tamerj1's avatar
    tamerj1
    4 years ago

    Hi Burak83_ 
    Please refer to sample file with the solution https://www.dropbox.com/t/S2iOGgF60q6tRzxL

    Cause (Calculated) = 
VAR CaseYearTable = CALCULATETABLE ( Incidents, ALLEXCEPT ( Incidents, Incidents[Department],Incidents[Description], Incidents[Year] ) )
VAR AnnualOccurrences = COUNTROWS ( CaseYearTable )
VAR TotalLostHours = Incidents[Lost hours]
RETURN
    IF ( AnnualOccurrences >= 4 || TotalLostHours >= 8, "Yes", "No" )

4 Replies

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi Burak83_ 
    Please refer to sample file with the solution https://www.dropbox.com/t/S2iOGgF60q6tRzxL

    Cause (Calculated) = 
VAR CaseYearTable = CALCULATETABLE ( Incidents, ALLEXCEPT ( Incidents, Incidents[Department],Incidents[Description], Incidents[Year] ) )
VAR AnnualOccurrences = COUNTROWS ( CaseYearTable )
VAR TotalLostHours = Incidents[Lost hours]
RETURN
    IF ( AnnualOccurrences >= 4 || TotalLostHours >= 8, "Yes", "No" )
    • Burak83_'s avatar
      Burak83_
      Regular Visitor
      4 years ago

      Hi TamerJ 

      Thanks for the solution this is exactly what I have asked for!! I have tried to solve this problem only with countrows or countx but it did not help me. I see that it requires extensive programming!