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

Have been pondering. Think above

works but is inefficient. Would be better to use FILTER with some

or statements. Share some

demo data and conditions that need to be met and will

rewrite.

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


Status OLInumber SDFlag OtherFlag SPFlag PFlag

active      001             Y            Y           N         Y
Lost         002            N            Y           Y          Y
Booked    003               Y            Y           Y        Y

 

For SDFlag the condiditons are when SDFlag = "Y" and Status = "Active"

For OtherFlag the conditions are when OtherFlag = "Y" and Status = "Active" or "Lost"
For SPFlag the conditions are when SPFlag = "Y" and Status = "Booked" and "Lost"

For PFlag the conditions are when PFlag = "Y" and Status = All OLIs so can just call the entire column bcdobbs 
Those are the individual requirments then with those counts the final formula. Then that will give  a percent to be used to conditionally format colors on the individual flag parameters. So SD flag is when Shipment < Close date so using the percent I get from the overall formula I need to shade shipment date when there is an issue on the oli picked up by the flag bcdobbs  - Here is the final formula
 - Here is an example of the defintion of the flag
With the above data the expected result would be an OLI count of 3. Then for each flag there would be a the calcaulate(countrows we did earlier to be added together to get the distinct OLI number result. There also could be overlapping OLIs within the same calculate condition so I guess I will need the distinct in the OLI and combinedset  bcdobbs 


  • bcdobbs's avatar
    bcdobbs
    Icon for Community Champion rankCommunity Champion
    4 years ago

    I think this would work better, do you want to try it with a larger dataset?

    Count Example = 

VAR SelectedRows = 
    FILTER (
        'Table1',
        ( 'Table1'[SDFlag] = "Y" && 'Table1'[Status] = "active" )
        || ( 'Table1'[OtherFlag] = "Y" && 'Table1'[Status] IN { "active", "Lost" } )
        || ( 'Table1'[PFlag] = "Y" )    
    )

RETURN COUNTROWS ( SelectedRows )
  • poweruser55's avatar
    poweruser55
    Icon for Helper IV rankHelper IV
    4 years ago

    So currently I have 9 different measure of these one for each flag. 

    OLIS_Expired = CALCULATE(
    DISTINCTCOUNT( '999_Funnel'[OLI Number] ),
    '999_Funnel'[Expired (Flag)] = "Y",
    '999_Funnel'[OLI.Status] = "Active",
    '999_Funnel'[OLI.Status] = "Signed")
    How can I combine the measures so they count the distinct oli in each? I tried doing Calculate(distinctcount(oli number), OLIS_Expired + ... ) but that did not work bcdobbs 

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

    I also tried your way with the union but the variable doesn't come up bcdobbs