Boolean expression

[DouweMeer]

Dear community,

I'm relative new to Power Bi, but experienced with Excel. 

I'm trying to get my head around the filter function in combination with calculate. I have the following expression: 

test 1 = if ( and ( [New Pending] = BLANK() , [Old Pending] = BLANK() )

, BLANK()

, if ( [New Pending] <> BLANK()

   , BLANK()

   , calculate (

      max ( [CreatedDate] ) ,

         filter ( 'Case History' , [NewValue] = "Pending" ) ,

         filter ( 'Case History' , [Casenumber] <> BLANK() ) ,

         filter ( 'Case History' , [Casenumber] = "00056265" ) ,

         filter ( 'Case History' , 'Case History'[CreatedDate] < date ( 2018 , 11 ,14 ) )

      )

   )

)

This expression does work and gives the correct result. But when I change the last bit to a flexible data field, I get nothing. In Excel I would use the maxifs formula for this situation. 

test 1 = if ( and ( [New Pending] = BLANK() , [Old Pending] = BLANK() )

, BLANK()

, if ( [New Pending] <> BLANK()

   , BLANK()

   , calculate (

      max ( [CreatedDate] ) ,

         filter ( 'Case History' , [NewValue] = "Pending" ) ,

         filter ( 'Case History' , [Casenumber] <> BLANK() ) ,

         filter ( 'Case History' , [Casenumber] = "00056265" ) ,

         filter ( 'Case History' , 'Case History'[CreatedDate] < 'Case History'[Old Pending] )

      )

   )

)

Practically what I got is a large changes table and I try to link the change ID's to each other for me to calculate the time that a case was on a certain status, as I need to substract weekends and holidays. Eventually, also the part with "00056265" will become flexible as the table contains 250K+ records, 80K+ cases and I'm not going through them one by one. 

It seems that the part of "1 = 2" of the Boolean expression one of both should be static in order to make it work, if you are only working with a single table. It appears (according to internet, haven't tried it yet) that you can use it, when the second variable is coming from another table. Isn't there a trick to mark the 1 or 2 in the Boolean Expression as the ID from the specific record the calculation is in, as comparison against a column?

----------

Update 

Made it work by creating a second and third table. 

New table 1:

Case Hist - Case No = DISTINCT( filter ( values ( 'Case History'[Casenumber] ) , LEN( 'Case History'[Casenumber] ) > 0 ) )

New table 2:

Case Hist - Created = DISTINCT( 'Case History'[CreatedDate] )

Adjusted filter:

test 1 = if ( and ( [New Pending] = BLANK() , [Old Pending] = BLANK() )

   , BLANK()

   , if (

      [New Pending] <> BLANK()

      , BLANK()

      , calculate (

         max ( [CreatedDate] ) ,

         filter ( 'Case History' , [NewValue] = "Pending" ) ,

         filter ( 'Case History' , [Casenumber] <> BLANK() ) ,

         filter ( 'Case Hist - Case No' , 'Case History'[Casenumber] = 'Case Hist - Case No'[Casenumber] ) ,

         filter ( 'Case Hist - Created' , 'Case Hist - Created'[CreatedDate] < 'Case History'[CreatedDate] )

      )

   )

)

Still remains the question whether you could do it from 1 table instead of multiple. 

[Zubair_Muhammad]

@DouweMeer

Check this article on filter arguments. Hope it will help

https://www.sqlbi.com/articles/filter-arguments-in-calculate/

When you write a boolean expression

1 2 3 4

CALCULATE (     <expression>,     table[column] = <value> )

In reality the filter expression you wrote is transformed into

1 2 3 4 5 6 7

CALCULATE (     <expression>,     FILTER (         ALL ( table[column] ),         table[column] = <value>     ) )

[DouweMeer]

@Zubair_Muhammad

It is a very interesting link and I've added it to my favorites, but it seems it is not an answer to my question.

I do see I've forgotten on the critical line to type the filter expression. I've updated my original post with the correct line, sorry for the inconvenience. Below the line I've updated

'case history'[CreatedDate] < [Old Pending] filter ( 'case history' , 'case history'[CreatedDate] < 'case history'[Old Pending] ) 

I want to assign [Old Pending] as the value in the record that has to be matched in column [CreatedDate]. Thus as a solution, I've now thrown [CreatedDate] in a seperate table with unique values. However, this is not ideal.