Unique count of a column in a measure with Countrows Filter and GroupBy in use?

[WAB_Reports]

Hello all,

I'm struggling with how to get a count of Unique "Unit Plate" based on the data below, with countrows a filter and groupby using "AGE YEARS" >2 and Tires = "OLD".

UNIT TYPE	UNIT PLATE	COLOUR	TIRES	AGE Years
CAR	XYZ1	RED	NEW	1
CAR	XYZ1	RED	NEW	1
CAR	XYZ36	BLUE	OLD	5
CAR	XYZ400	RED	OLD	6
CAR	XYZ5	BLUE	NEW	2
CAR	XYZ1	BLUE	OLD	5
CAR	XYZ9	GREEN	NEW	3
CAR	XYZ9	GREEN	NEW	3
CAR	XYZ400	RED	OLD	6

The table is much larger than this and if I do a manual filter (with just the above data) the value comes back as 3 (Age Years??) instead of 4.

Secondary quick question: is there a step through available in Power Bi? Would be awesome to have the ability to see your table change as you compose the query / measure etc.

[WAB_Reports · Accepted solution]

Hmm now I have an issue with accepting the solutions, I combo'd both to get what I needed. 🙂

I ended up with:

CountUniquePlates = CALCULATE(DistinctCount('Table'[UNIT PLATE]), FILTER('Table', 'Table'[AGE Years]> 2 && 'Table'[TIRES] ="OLD"))

Thank you to all who replied.

[mahoneypat]

Please try this expression

NewMeasure = CALCULATE(DISTINCTCOUNT(Table[Unit Plate]), Table[Age Years]>2, Table[Tires] = "Old")

There is not a DAX editor like the query editor (that would be greate); however, you can approach that by visualing your filter/table expressions using a DAX table and/or breaking up your expressions into variables and "return"ing each individually to troubleshoot.  This expression doesn't need either, but for more complex queries those approaches come in very handy.

Regards,

Pat

[NamishB]

Hi @WAB_Reports - Try below measure:

CountMeasure = CALCULATE(Count('Table'[UNIT PLATE]), FILTER('Table', 'Table'[AGE Years]> 2 && 'Table'[TIRES] ="Old"))

This will give you output as 4. 

Hope this helps.

Cheers,

-Namish B