Looking up values in a column

[PHEstaciMa1]

Hi Everyone, I wanted to seek your help in my current DAX formula, 

 however, it says "Too many arguments were passed to the COUNTROWS function. The maximum argument count for the function is 1".

I wanted to count the occurrence of a particular variable in a column. Below is the variable I wanted to know its occurrence.

And below column where I wanted to look for the number of its recurrence. The rows in the column contains multiple variable. 

I'm writing it wrong, appreciate help from the team. thank you very much!

[v-easonf-msft]

Hi, @PHEstaciMa1 

If your problem has been solved, please accept the reply as solution to close this thread, so that other community members will easily find the solution when they get the same issue.

If you still need help, please share more details.

Best Regards,
Community Support Team _ Eason

[PHEstaciMa1]

Hi @v-easonf-msft sorry, I had issues in my software last week. I tried your advise but it showed an error.

I'm trying to look for ways on how can I share my PBIX until now. Hopefully I can find a way.

[PHEstaciMa1]

Hi Everyone, can you try this link.

https://www.dropbox.com/t/XqDquCw35aunykXY 

[tamerj1 · Accepted solution]

Hi @PHEstaciMa1 
Here is the file with solution. https://we.tl/t-dixeoricuZ
Hope it satisfies your requirement

Antecedents LSR - Tamer = 
VAR CurrentValue = 
    MAX ( 'Antecedents - LSR'[Value] )
RETURN
    SUMX (
        '2022 Gemba Online',
        IF ( CONTAINSSTRING ( '2022 Gemba Online'[Antecedents], CurrentValue ), 1, 0 )
    )

[PHEstaciMa1]

Thanks @v-easonf-msft Yes, this is what I wanted to be. 

[v-easonf-msft]

Hi, @PHEstaciMa1 

The 'Max' should be used for the first parameter in 'CONTAINSSTRING', please modify your original formula as below:

M_Antecedents LSR = 
COUNTROWS (
    FILTER (
         ALL('Antecedents - LSR'),
         CONTAINSSTRING (
            MAX('2022 Gemba Online'[Antecedents]) ,
            'Antecedents - LSR'[Value]
        )
    )
) + 0

Please check my sample file for more details.

Best Regards,
Community Support Team _ Eason

[tamerj1]

@v-easonf-msft @PHEstaciMa1 
My understanding was acutally the opposite. Maybe I'm wrong but I thought @PHEstaciMa1 wants to slice by [Value] and count how many times this value occures in the Antecedents list. 
*Update..

Here is @v-easonf-msft sample file with my solution https://www.dropbox.com/t/GuXjvUFexvxMNo1H

Antecedents LSR - Tamer = 
VAR CurrentValue = 
    MAX ( 'Antecedents - LSR'[Value] )
RETURN
    SUMX (
        '2022 Gemba Online',
        IF ( CONTAINSSTRING ( '2022 Gemba Online'[Antecedents], CurrentValue ), 1, 0 )
    )

[PHEstaciMa1]

@tamerj1 do you still have the pbix file? I can't access in your dropbox. thanks

[tamerj1]

@PHEstaciMa1 

I guess so. Let me check it up and forward it to you

[PHEstaciMa1]

Hi @tamerj1 thanks! do you need my email? 

[tamerj1]

@PHEstaciMa1 
Sorry I was out of office. Please refer to https://we.tl/t-zlFIQwAWjX

[PHEstaciMa1]

Hi @v-easonf-msft I'll have a look at your work first and provide you feedback. yes I wanted to count the occurrences of a particular value in a column which contains other value as well. I'm sharing the PBIX thru this link.

PBIX Help Needed 

[tamerj1]

@PHEstaciMa1 
The link you have shared requires log in with your acount. Did you check my last reply? I have modified @v-easonf-msft sample file and re-shared it in the other comment. Please check and let me know if this is what you want. Thank you

[tamerj1]

Hi @PHEstaciMa1 

you may try

Antecedents LSR =
COUNTROWS (
    FILTER (
        '2822 Gemba Online',
        '2022 Gemba Online'[Antecedents] IN VALUES ( 'Antecedents LSR'[Value] )
    )
)

[PHEstaciMa1]

hi @tamerj1 tried but it didnt work.

No figures were shown.

[tamerj1]

@PHEstaciMa1 
Just double checked with dummy data. The code is working. What are you slicing by? I guess you should be slicing by 'Antecedents LSR'[Value] in order to see the count related to each value. Am I missing something?

[tamerj1]

@PHEstaciMa1 

You can upload the file to OneDrive or WeTransfer and semd the link. 

[amitchandak]

@PHEstaciMa1 , better to create a new column in table1

countx(filter(Table2, Containsstring(Table2[Antecednet], Table1[Value]) ), Table2[Antecednet])

If this does not help
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Then you can sum

[PHEstaciMa1]

Hi @amitchandak  I'm trying to share the PBIX but it seems there is no option in the reply to attached files. Where can I send it?

[amitchandak]

@PHEstaciMa1 , you can upload on onedrive or dropbox and share the link