Forum Discussion

lea_313's avatar
lea_313
Icon for Helper I rankHelper I
4 years ago
Solved

Count based on multiple criteria

Hiya   I need to calculate some counts but its proving a bit tricky   Sample data below   Project code ID Product for Month Year Revenue Product Header Stage 1544 Class 1 January ...
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    lea_313 , so you actually do want to count up the number of [Project code ID] that are active? Then I think this measure will work:

    Active Project Count = 
    SUMX(
        VALUES('lea_313'[Project code ID])
        ,MAXX('lea_313', IF(FIND("100%", 'lea_313'[Stage], 1, 0) = 0, 1, 0))
    )
Anonymous's avatar
Anonymous
Not applicable
4 years ago

lea_313 , I believe this measure will give you the desired results:

Has Active Project = 
    MAXX(
        'YourTable'
        , VAR vStagePercentPosition = FIND("%", 'YourTable'[Stage], 1, 0) -1
        VAR vStagePercent = VALUE(LEFT('YourTable'[Stage], vStagePercentPosition))
        RETURN
            IF(vStagePercent < 100, "Y", "N")
    )
  • lea_313's avatar
    lea_313
    Icon for Helper I rankHelper I
    4 years ago

    I get the following:

    Feedback Type:
    (Error)

    MdxScript(Model) (47, 29) Calculation error in measure [Active Project]: Cannot convert value 'G. 100' of type Text to type Number.

     

    My actual stage is anything that is not G.100% Delivered

     

    Thanks

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      lea_313 , in the sample data you provided you did not mention that the Stage column can contain values such as "G. 100%". Try this alternative measure:

      Has Active Project = 
    MAXX(
        'YourTable'
        , VAR vStagePercentPosition = FIND("100%", 'YourTable'[Stage], 1, 0) 
        RETURN
            IF(vStagePercentPosition = 0, "Y", "N")
    )

       

      • lea_313's avatar
        lea_313
        Icon for Helper I rankHelper I
        4 years ago

        Hiya

         

        Thanks for your help

         

        I've added the measure. I get a Y/N rather than a count of active projects by project code. How do I convert this to a count please?

         

        For example, there are 10 active Coaching, 5 active team in 2022

         

        Thanks