Specific count with condition and runs need to add

[ramie]

Hi Everyone,

I have a problem and i am unable to fix that.

In my report , i have code column that contains these extension codes /LH , /LE,/LS/ and /DE,/DS,/DH. In every month many codes were created, now the scenario is, i need to count only /L extension codes and if count =1 or 2or3or4, we need add runs like this

count of code * 5 runs and if count >= 10 or more we need to add 50 runs, 50 is final. These runs i need to add to a measure and this shold be happened in every month.

i have tried this formula, but not getting output ,

rowscounting 2 =

VAR LCOUNT =

CALCULATE(

'CARL_CS02 CSWO_WO'[WO COUNT],# count of code

FILTER(

'CARL_CS02 CSWO_WO',

'CARL_CS02 CSWO_WO'[WO FILTER] in {"/LH WO","/LS WO","/LE WO"}#WO FILTER is custom column holding just extension of code

)

)

VAR ADDINGRUNS =

IF(LCOUNT = 0, 'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT = 1, 5+'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT = 2, 10+'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT = 3, 15+'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT = 4, 20+'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT = 5, 25+'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT = 6, 30+'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT = 7, 35+'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT = 8, 40+'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT = 9, 45+'DAX FORMULAS'[L1-SCORE],

IF(LCOUNT >= 10, 50+'DAX FORMULAS'[L1-SCORE],0

)))))))))))

RETURN

LCOUNT

please let me know is their any way to fix and to get correct output.

Thanks for your contribution, co-operation and helping

[Samarth_18]

Hi @ramie ,

Its difficult to answer without seeing data but based on what you are trying below could be ideal code:-

rowscounting 2 =
VAR LCOUNT =
    CALCULATE (
        SUM ( 'CARL_CS02 CSWO_WO'[WO COUNT] ),
        FILTER (
            'CARL_CS02 CSWO_WO',
            CONTAINSSTRING ( 'CARL_CS02 CSWO_WO'[WO FILTER], "/L" )
        )
    )
VAR ADDINGRUNS =
    IF (
        LCOUNT = 0,
        'DAX FORMULAS'[L1-SCORE],
        IF (
            LCOUNT = 1,
            5 + 'DAX FORMULAS'[L1-SCORE],
            IF (
                LCOUNT = 2,
                10 + 'DAX FORMULAS'[L1-SCORE],
                IF (
                    LCOUNT = 3,
                    15 + 'DAX FORMULAS'[L1-SCORE],
                    IF (
                        LCOUNT = 4,
                        20 + 'DAX FORMULAS'[L1-SCORE],
                        IF (
                            LCOUNT = 5,
                            25 + 'DAX FORMULAS'[L1-SCORE],
                            IF (
                                LCOUNT = 6,
                                30 + 'DAX FORMULAS'[L1-SCORE],
                                IF (
                                    LCOUNT = 7,
                                    35 + 'DAX FORMULAS'[L1-SCORE],
                                    IF (
                                        LCOUNT = 8,
                                        40 + 'DAX FORMULAS'[L1-SCORE],
                                        IF (
                                            LCOUNT = 9,
                                            45 + 'DAX FORMULAS'[L1-SCORE],
                                            IF ( LCOUNT >= 10, 50 + 'DAX FORMULAS'[L1-SCORE], 0 )
                                        )
                                    )
                                )
                            )
                        )
                    )
                )
            )
        )
    )
RETURN
    ADDINGRUNS