Multiple conditions in measure

[poweruser55]

How can I do multiple conditions in a measure? Right now I have this - 

Ship = calculate(count('Table1'[OLI Number]),filter('Table1','Table1'[SD (Flag)]="Y")+0, filter('Table1','Table1'[Status]="Active"))
But this does not work, I need two condiitons, the flag = "Y" and Status= "Active" how can I do this?

[bcdobbs · Accepted solution]

Try the following. Only thing I wasn't sure on was what you were trying to do with the +0. Are you trying to remove blanks? I don't think it's needed with a predicate like that.

Ship =

CALCULATE (

   COUNTROWS ( 'Table1' )

   'Table1'[SD (Flag)] = "Y",

   'Table1'[Status] = "Active",

   NOT ( ISBLANK ( 'Table1'[OLI Number] ) )

)

[poweruser55]

The number still seems way too high.. That filters both in that table for Flag = Y and status = active? @bcdobbs 

[bcdobbs]

Yes, it should be!

If you go into the table view you should be able to apply the same filters and see what rows you have which might give a clue as to why it's too high.

[poweruser55]

Ok so if I wanted to add the same thing but chnage the status to = lost I could just copy the formula and add a + sign and change the status. So I need all conditions to be true for each separate statement.  

CALCULATE (

   COUNTROWS ( 'Table1' )

   'Table1'[SD (Flag)] = "Y",

   'Table1'[Status] = "Active",

   NOT ( ISBLANK ( 'Table1'[OLI Number] ) +

CALCULATE (

   COUNTROWS ( 'Table1' )

   'Table1'[SD (Flag)] = "Y",

   'Table1'[Status] = "Lost",

   NOT ( ISBLANK ( 'Table1'[OLI Number] ) )

)

 )

) I just need to make sure I am not double counting. Also why do you not need to put a filter function? I thought that would be needed for each column. @bcdobbs 

[bcdobbs]

Yes that's right just need a calculate for each expression and can indeed separate with + sign.

So for single column filters like the ones above, CALCULATE can take a simple predicate like Table1[Status] = "lost". Under the surface it turns it into a full filter statement itself. It's referred to as syntax sugar; just makes it easier to read.

[poweruser55]

I posted this here, it is using the same logic as this question if you could please take a look. I'm very stuck here. https://community.powerbi.com/t5/Desktop/Count-at-least-one-flag/m-p/2390129 @bcdobbs 

[poweruser55]

I have a few of these flag columns I need to add together. The problem is I don't want to double count them. Using the oli number to count how can I make sure that it is only counting a unique oli number for each measure that contains the flag and other criteria? @bcdobbs for example I have another measure 

CALCULATE (

   COUNTROWS ( 'Table1' )

   'Table1'[Another (Flag)] = "Y",

   'Table1'[Status] = "Active",

   NOT ( ISBLANK ( 'Table1'[OLI Number] ) 

I need another measure that counts each oli from both measure but it has to be unique so it doesn't double count. Because an oli number can have any number of flags. @bcdobbs Basicslly each measure counts then I need to add up all the measures together and count for when there is AT LEAST one Oli number with a flag. But if it has 3 flags I only want one to count for the total.

[bcdobbs]

Ok in that case you don't want to add sepearate calculates.

Instead do it all inside one calculate with a set of filters that return all possible rows including duplicates. Then use DISTINCTCOUNT of your oli column instead of COUNTROWS.

[poweruser55]

I don't think that will work. There are overlapping filters. They have to be done separately then combined. Separate measures then a combined count of the distinct oli numbers @bcdobbs