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

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