topic Re: Looking up values in a column in DAX Commands and Tips

Looking up values in a column

PHEstaciMa1 — Mon, 18 Apr 2022 08:34:36 GMT

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!

Re: Looking up values in a column

PHEstaciMa1 — Mon, 18 Apr 2022 08:44:49 GMT

amitchandak requesting help in my problem with DAX using PBI desktop. thanks!

Re: Looking up values in a column

amitchandak — Mon, 18 Apr 2022 09:32:42 GMT

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

Re: Looking up values in a column

tamerj1 — Mon, 18 Apr 2022 09:47:12 GMT

Hi PHEstaciMa1

you may try

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

Re: Looking up values in a column

PHEstaciMa1 — Tue, 19 Apr 2022 09:25:48 GMT

hi tamerj1 tried but it didnt work.

No figures were shown.

Re: Looking up values in a column

PHEstaciMa1 — Tue, 19 Apr 2022 14:02:40 GMT

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?

Re: Looking up values in a column

tamerj1 — Tue, 19 Apr 2022 14:17:00 GMT

PHEstaciMa1

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

Re: Looking up values in a column

amitchandak — Tue, 19 Apr 2022 17:12:37 GMT

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

Re: Looking up values in a column

tamerj1 — Wed, 20 Apr 2022 11:00:38 GMT

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?

Re: Looking up values in a column

v-easonf-msft — Thu, 21 Apr 2022 07:17:09 GMT

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

Re: Looking up values in a column

tamerj1 — Thu, 21 Apr 2022 07:45:54 GMT

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 ) )

Re: Looking up values in a column

PHEstaciMa1 — Thu, 21 Apr 2022 07:35:42 GMT

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

Re: Looking up values in a column

PHEstaciMa1 — Thu, 21 Apr 2022 07:36:56 GMT

Hi amitchandak tamerj1 I'm sharing to both of you my PBIX file, removed the sensitive information already. You may access the file here.

PBIx Help Needed

Re: Looking up values in a column

tamerj1 — Thu, 21 Apr 2022 08:06:56 GMT

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

Re: Looking up values in a column

v-easonf-msft — Tue, 26 Apr 2022 07:43:38 GMT

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

Re: Looking up values in a column

PHEstaciMa1 — Tue, 26 Apr 2022 08:30:33 GMT

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.

Re: Looking up values in a column

PHEstaciMa1 — Tue, 26 Apr 2022 08:35:04 GMT

Hi Everyone, can you try this link.

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

Re: Looking up values in a column

tamerj1 — Tue, 26 Apr 2022 08:48:06 GMT

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 ) )

Re: Looking up values in a column

PHEstaciMa1 — Tue, 26 Apr 2022 09:11:38 GMT

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

Re: Looking up values in a column

PHEstaciMa1 — Fri, 03 Jun 2022 05:52:00 GMT

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