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

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

If DISTINCTCOUNT is not an option and you have to control very specific sets of filters I think you'd have to go down this sort of route... (away from laptop typing on phone so apologise for any syntax issues).

 

CountMeasure =
VAR OliSet1 =

CALCULATETABLE (

   DISTINCT ( 'Table1'[OLI Number] ),

   Condition1,

   Condition2

)

 

VAR OliSet2 =

CALCULATETABLE (

   DISTINCT ( 'Table1'[OLI Number] ),

   Condition1,

   Condition2

)

 

VAR OliSet3 =

CALCULATETABLE (

   DISTINCT ( 'Table1'[OLI Number] ),

   Condition1,

   Condition2

)

VAR CombinedSet =

DISTINCT (
UNION ( OliSet1, OliSet2, OliSet3 )

)

 

RETURN COUNTROWS ( CombinedSet )

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

    So each oli set will have distinct OLI numbers? Like if the filters produce the same oli number for oli set1 and 2(ex:001)it will only count 001 once in the total? The way I'm reading that is that it's distinct for each conditions. But I want the oli number distinct for each oli set when the total is calculated. bcdobbs 

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

      Can you share a set of test data. As said not by a laptop at moment but can try later.

       

      The code I sent:

      1) Builds distinct lists for each set of conditions.

      2) Appends them all together (union) 

      3) Removes duplicates (DISTINCT wrapped round the union).

      4) Counts the rows that are left.

      • 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

    I posted expected result below just in case I wasn't clear. But maybe your solution already solves this just want to make sure. bcdobbs 

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

    I just tried the union and it ended up giving me my highest number for one of the flags. I guess this means that this one flag was also a Y in the other columns? bcdobbs 

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

      Hi poweruser55 ,

       

      Has your problem been solved? If it is solved, please mark a reply which is helpful to you.

       

      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