Forum Discussion

love's avatar
love
Helper I
8 years ago
Solved

How to count rows with additional logic (countrows/countx)

Hello.

 

I have a data set that looks like this:

 

 

Task	Config.	Time
Task 1	A	20
Task 1	A	60
Task 1	B	214
Task 2	A	8
Task 2	B	215
Task 3	A	67
Task 3	B	21
Task 3	B	57

 

If I create a measure like "= COUNTROWS(Tablename)" it will return 8, because there are 8 rows. What I would like is to only include unique Task + Config combinations in the count, meaning that "Task 1 - A" and "Task 3 - B" are only counted once, instead of twice (because they have 2 different time values, which I would like to not be included in the count) and that the returned value is 6, not 8.

 

I played around a little with COUNTX but was not able to achieve what I am trying.

 

Thank you for your help,

-L

Need Help
  • TomMartens's avatar
    TomMartens
    8 years ago

    Hey,

     

    maybe this measure provides what you are looking for.

    Countrows Summarize = 
COUNTROWS(
    SUMMARIZE('Table1'
        ,Table1[Task]
        ,Table1[Config.]
    )
)

    It results to 6.

     

    Using SUMMARIZE allows to create a table using just the columns you want.

     

    You can also use
    ADDCOLUMNS(
        SUMMARIZE(...)

        ,"NewColumnName 1", Expression

        ,...
    )

     

    For more examples using SUMMARIZE and ADDCOLUMNS please have a look here:

    https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

     

    Hopefully this is what you are looking for

     

    Regards

    Tom

4 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    8 years ago

    You could add a calculated column that does a CONCATENATE of your two columns and then write a measure that does a DISTINCTCOUNT on that new column.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago
      Measure =
      COUNTROWS ( SUMMARIZECOLUMNS ( Table1[Task], Table1[Config.] ) )

      this measure creates a table containg all distinct values of the task and config columns and then returns the number of rows in said table

       

      edit: Tom Martens post does the same method just with summarize and add columns instead of summarizecolumns. They should produce the same result and perform the same, summarizecolumns just has simpler syntax.

       

      https://www.sqlbi.com/articles/introducing-summarizecolumns/ to read more about this

      • love's avatar
        love
        Helper I
        8 years ago

        Hi Greg_Deckler, TomMartens & Anonymous!

         

        Thank you all for your super quick replies and the proposed solutions - much appreciated. I tried all of them and they work just fine and provide me with what I need. I ended up using TomMartens proposal, it seemed like the most simple solution for my actual data set.

         

        Thanks again and have a nice day!

        -L

  • TomMartens's avatar
    TomMartens
    Super User
    8 years ago

    Hey,

     

    maybe this measure provides what you are looking for.

    Countrows Summarize = 
COUNTROWS(
    SUMMARIZE('Table1'
        ,Table1[Task]
        ,Table1[Config.]
    )
)

    It results to 6.

     

    Using SUMMARIZE allows to create a table using just the columns you want.

     

    You can also use
    ADDCOLUMNS(
        SUMMARIZE(...)

        ,"NewColumnName 1", Expression

        ,...
    )

     

    For more examples using SUMMARIZE and ADDCOLUMNS please have a look here:

    https://www.sqlbi.com/articles/best-practices-using-summarize-and-addcolumns/

     

    Hopefully this is what you are looking for

     

    Regards

    Tom