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