Forum Discussion

PHEstaciMa1's avatar
PHEstaciMa1
Icon for Helper II rankHelper II
4 years ago
Solved

Looking up values in a column

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!

 

 

  • tamerj1's avatar
    tamerj1
    4 years ago

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

22 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    Hi PHEstaciMa1 

    you may try

    Antecedents LSR =
COUNTROWS (
    FILTER (
        '2822 Gemba Online',
        '2022 Gemba Online'[Antecedents] IN VALUES ( 'Antecedents LSR'[Value] )
    )
)
  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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's avatar
      tamerj1
      Icon for Community Champion rankCommunity Champion
      4 years ago

      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's avatar
        PHEstaciMa1
        Icon for Helper II rankHelper II
        4 years ago

        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 

  • v-easonf-msft's avatar
    v-easonf-msft
    Icon for Community Support rankCommunity Support
    4 years ago

    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