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

[love]

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

[TomMartens · Accepted solution]

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]

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]

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]

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