Containsstring and Count

[AllanBerces]

Hi good day 

Can anyone help me on my calculated column, i have two table that connected. I want to count the number of Job.

DESIRED OUTPUT

Table Connection

 JC Count =

CALCULATE(

    COUNTROWS(MAIN_DATA),

    FILTER(

        'MAIN_DATA',

        (CONTAINSSTRING('MAIN_DATA'[Job No.], "LP") || CONTAINSSTRING('MAIN_DATA'[Job No.], "LS") || CONTAINSSTRING('MAIN_DATA'[Job No.], "LI") || CONTAINSSTRING('MAIN_DATA'[Job No.], "LM"))

        &&

        NOT CONTAINSSTRING('MAIN_DATA'[Job No.], "QF")

        &&

        NOT CONTAINSSTRING('MAIN_DATA'[TQ], "SQ")

    )

)

Thank you

[Ahmedx · Accepted solution]

Sorry, I optimized the code.

[Ahmedx]

попробуй удалить ALL

L Count = 
VAR _KewSearch= SELECTCOLUMNS( {"LS","LP"},"KeyWord",[Value])
 VAR _Result = COUNTROWS(FILTER('MAIN_DATA TABLE','MAIN_DATA TABLE'[Code] in _KewSearch))
RETURN
_Result

[AllanBerces]

Hi @Ahmedx thank you but show same, it count all not on the specific Main No.

[Ahmedx]

try 

[AllanBerces]

Hi @Ahmedx thank you for the reply but the new calculated column we required is for the current table.

[Ahmedx]

or creat new columns in Current

L = SUMX(RELATEDTABLE('MAIN_DATA TABLE'),[L-Count])

Q = SUMX(RELATEDTABLE('MAIN_DATA TABLE'),[Q-Count])

[Ahmedx]

Post the file, I don't know what you have there.(current)

[AllanBerces]

Hi @Ahmedx i have this column on my current table plus the new calculated column (L and Q Count)

Thank you

[Ahmedx · Accepted solution]

Sorry, I optimized the code.

[AllanBerces]

Hi @Ahmedx thnak you very much working perfectly

[Ahmedx · Accepted solution]

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly

[Ahmedx]

try

[Ahmedx]

pls try

Q Count = 
VAR _KewSearch= SELECTCOLUMNS( {"QP","QF","QK","QL"},"KeyWord",[Value])
VAR _Cod = MAX('MAIN_DATA TABLE'[Main No])
 VAR _Result = COUNTROWS(FILTER(ALL('MAIN_DATA TABLE'),'MAIN_DATA TABLE'[Code] in _KewSearch&&'MAIN_DATA TABLE'[Main No]=_Cod))
RETURN
_Result
-----------------
L Count = 
VAR _KewSearch= SELECTCOLUMNS( {"LS","LP"},"KeyWord",[Value])
VAR _Cod = MAX('MAIN_DATA TABLE'[Main No])
 VAR _Result = COUNTROWS(FILTER(ALL('MAIN_DATA TABLE'),'MAIN_DATA TABLE'[Code] in _KewSearch&&'MAIN_DATA TABLE'[Main No]=_Cod))
RETURN
_Result

[AllanBerces]

Hi @Ahmedx it work if i used measure but we required is calculated column to current table

Thank you

[AllanBerces]

Hi @Ahmedx can i use this as calculated column

[AllanBerces]

Hi @Ahmedx thank you for the reply but it shows blank

[Ahmedx]

pls try

[AllanBerces]

Hi @Ahmedx thank you for the reply but it count the overall instead of per Main No. Please note my MAIN DATA TABLE, Main No column has many different no.

DESIRED OUTPUT

Thank you