Forum Discussion

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

Count at least one flag

I have an app that calculates a few KPIS using flags.   It does this by using count of the flag. From there I need to add all of the counts together and divide by the total status count. There could be overlapping counts because one OLI can have multiple kpi flags. What would the formula be for when there is at least one flag counted divided by total status. I can't just add all the kpi counts because I would be double counting. This is an example: measure1 = 

CALCULATE (

   COUNTROWS ( 'Table1' )

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

   'Table1'[Status] = "Active",

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

measure2 = 

CALCULATE (

   COUNTROWS ( 'Table1' )

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

   'Table1'[Status] = "Active",

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

 

so from both of those measures I need to count the unique oli numbers 

Need Help

17 Replies

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    Can this be the denominator

    =CALCULATE(COUNTROWS('Table1'),'Table1'[Status] = "Active",NOT(ISBLANK('Table1'[OLI Number])))

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

      Why though? I need all the conditions in the measure. Then I need to count the distinct olis from each measure. So the entire measure needs to be filtering for those 3 conditions. Ashish_Mathur 

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        4 years ago

        Hi,

        Share data in a simple table format and show the expected result there.

  • v-kkf-msft's avatar
    v-kkf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    Hi poweruser55 ，

     

    I am not sure if I understood your question correctly. Please try the following formula:

     

    Measure = 
CALCULATE (
    DISTINCTCOUNT ( '999_Funnel'[OLInumber] ),
    FILTER (
        VALUES ( '999_Funnel'[OLInumber] ),
        [OLIS_SDFlag] + [OLIS_OtherFlag] + [OLIS_SPFlag] + [OLIS_PFlag] > 0
    )
)

     

    If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
    Best Regards,
    Winniz
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.