SUMPRODUCT DAX

[Anonymous]

Hi,

I have a calculation row by row in excel that is;

=(IF(SUMPRODUCT(($U$2:$U3=U3)*($T$2:$T3=T3))>1,0,1))*TotalCoreHours

=(IF(SUMPRODUCT(($U$2:$U4=U4)*($T$2:$T4=T4))>1,0,1))*TotalCoreHours

=(IF(SUMPRODUCT(($U$2:$U5=U5)*($T$2:$T5=T5))>1,0,1))*TotalCoreHours

etc..

I was just wondering if there was an equivalent in Power BI as it needs to be row by row, as I couldnt see in previous threads?

I've seen about SUMx but I'm not sure if that would work the same

Thanks

Liam

[Geradav]

Hi @Anonymous 

Help us understand a bit better your function.

Your SUMPROD() is equivalent to a logical AND() where you compare all previous values of a column up to the current record to the value of the current record for 2 columns, column U and column T.

If both,
- a value in a record of column U up to current record is equal to current record column U
AND  
- a value in the same record of column T up to current record is equal to current record column T

Then

the result should be 0

Else

It's 1

In DAX you need to uniquely identify each record.

Then you would create a measure that would look like this:

myMeasure =
//Declare  variables storing the value of the current row for the column U and T in the current row context
  VAR currentRowColU = myTable[colName_U] 
  VAR currentRowColT = myTable[colName_T]

RETURN
IF (
  COUNTROWS (
    FILTER (
      myTable,
      myTable[colName_U] = currentRowColU
      && myTable[colName_T] = currentRowColT
    )
  ) > 1, 0, 1 * TotalCoreHours
)

Though it is a bit difficult without seeing your data. Could you provide some sample data to better understand how we can help?

Best

David

[Anonymous]

@Geradav ,

What you have said is essentially bang on, I just dont know whether its possible to have a formula for calculated column level?

Thanks

[Geradav]

@Anonymous 

I came up with a DAX calculated column as follow:

NewBookableHours = 
VAR TotalBookableHours = 1280
VAR CurrentRoom = ClassRoomTable[Room]
VAR CurrentBuilding = ClassRoomTable[Building Name]
VAR CurrentID = ClassRoomTable[ID]
VAR PreviousBookingTbl =
FILTER (
    ClassRoomTable,
    ClassRoomTable[ID] <= CurrentID
)
VAR CountExistingRecords =
COUNTROWS (
    FILTER (
        PreviousBookingTbl,
        [Room] = CurrentRoom
        && [Building Name] = CurrentBuilding
    )
)

RETURN
IF ( CountExistingRecords > 1, 0, TotalBookableHours )
    

Note that in this DAX statement I used the ID column to filter to previous records imagining they are naturally preceding the current record.
However, you could have a date and time column that could do that better. If that is the case you could replace the referencing to ID column with the date and time column.

Hope that is helpful

Best

David

[Anonymous]

Hi @Geradav ,

Thankyou so much for taking the time to help with this, its muc h appreciated.

After entering the calculated column query it says 'We're working on it'  for about 15 minutes. Its possibly too big of a query?

I essentially need bookable hours but if a row has the same building and room entry I dont want it to count it twice.

I wonder if its more suited to a measure, but it would have to ensure it only counted once for that Building and room.

[Anonymous]

Hi @Geradav ,

Many thanks for getting back. Ive attached the spreadsheet that I would be working from.

Link To Spreadsheet 

Essentially it just has to be row by row displaying 1280 or 0 E.g. in Power BI, so it has to be by calculated column if possible.

I hope that makes sense,

Thanks

Liam

[Anonymous]

@Geradav 

The Power BI data will be replicated by the spreadsheet

[Geradav]

Actually my DAX should be updated to first filter the table up to the current row before evaluating each record to the current row value.

David

[amitchandak]

@Anonymous 

It should be used like

sumx(Table, Table[Col1]*Table[Col2]) *[Measure2]

Or

sumx(Table, Table[Col1]*Table[Col2]*Table[Col3])

[Anonymous]

Hi @amitchandak,

With the SUMPRODUCT aspect counting the text in EXCEL, how would I get around this error?

Thanks in advance

Liam