Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Need help comparing rows and after that counting

Hello everyone, I have a dataset a little more complicated but in the same direction as this: I can arrange in 2 types: Type1: Subject Name1 State1 Name2 State2 Name3 State3 Name4...
  • Anonymous's avatar
    Anonymous
    3 years ago

    I managed to solve my problem using the method of the accepted solution in here . Just added one more filtering to the calculate.

NikhilChenna's avatar
NikhilChenna
Skilled Sharer
3 years ago

Hi Anonymous ,

It is bit confusing to undertand.

Can you paste your desired output according to the Type 1 and Type 2 table.

Waiting for you resposne Anonymous .

 

Regards,

Nikhil Chenna

 

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi NikhilChenna ,
    Sorry for the confusion.

    The fact is that I want the same output. I can have the table in 2 different types as I shown.
    Let's take the second. 
    For Subject 1 I want the output 1 cause it needs to count only one name within the state 2 or 3. Only the first name ABC has the valid state for me . The rest of them have invalid states for me (not 2 or 3).


    If we look into the C subject it has 4 names and there it need to count 2 because even if I have 1 name that appears 2 or multiple times with valid state (2 or 3), it need to count only 1 single time that name. So ABD-2 ABD-3 ABD-4 counts 1 time and PRO-2 counts 1 time so COUNT=2 (Out of 4 names, 3 have a valid state but one name with valid state that has the same value with another one with different or even same state need to count 1 not 2)

     

    Hope it is more clear!

     

    • NikhilChenna's avatar
      NikhilChenna
      Skilled Sharer
      3 years ago

      Hi Anonymous ,

      If you are looking for a output like this below, then follow the below steps.

      1. you have to make a calculated column in the table , using the below dax.

      Check = IF('Table (2)'[State]=2 || 'Table (2)'[State]=3,1,0)
       
      2. After that you have to create a new table from your table, go to report view, click on the modelling tab from the above ribbion, click on the new table icon. after that use the below dax to create a summarize table.
       
      Table 2 =
      SUMMARIZE(
          'Table (2)',
          'Table (2)'[Subject],'Table (2)'[Name],
          "MAX check",MAX('Table (2)'[Check])
      )
       
      Which will give you the output as shown in the above screen shot.
       
      Regards,
      Nikhil Chenna
       
      Appreciate with a Kudos!! (Click the Thumbs Up Button)
      Did I answer your question? Mark my post as a solution!
      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi NikhilChenna ,

        Not really. I want the output to be like this 

        To have in the column output the count for the whole Subject