cancel
Showing results for
Did you mean:
Highlighted
Member

## Boolean expression

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] )

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.
2 REPLIES 2
Super User

## Re: Boolean expression

@DouweMeer

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

When you write a boolean expression

 1 2 3 4 `CALCULATE` `(` `    ``, ` `    ``table``[column] = <``value``> ` `)`

In reality the filter expression you wrote is transformed into

 1 2 3 4 5 6 7 `CALCULATE` `(` `    ``, ` `    ``FILTER` `(` `        ``ALL` `(` `table``[column] ``)``,` `        ``table``[column] = <``value``> ` `    ``)` `)`

Member

## Re: Boolean expression

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.

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 66 members 1,186 guests
Recent signins: