Forum Discussion

poweruser55's avatar
poweruser55
Icon for Helper IV rankHelper IV
4 years ago
Solved

Multiple conditions in measure

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]="Ac...
Need Help
  • bcdobbs's avatar
    bcdobbs
    4 years ago

    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's avatar
poweruser55
Icon for Helper IV rankHelper IV
4 years ago

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's avatar
bcdobbs
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
    poweruser55
    Icon for Helper IV rankHelper IV
    4 years ago

    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's avatar
      bcdobbs
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
        poweruser55
        Icon for Helper IV rankHelper IV
        4 years ago

        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