Count Distinct Column B where Column A Match

[jvanmeter]

I need a little help with this one as i have not found an answer that works. 

I have two columns.

Column A     Column B

Group A       Name A

Group A       Name B

Group A       Name C

Group B       Name A

Group B       Name D

Group B       Name B

Group B       Name C

I want to Group Column A and count distinct on Column B Essentially so that it looks like this without actually having to create a new table.

Column A     Column B

Group A        3

Group B        4

So I want to be able to show this in a visual where below is true 

Total Number of Column A Groups  Where Column B is > 100 

Hope That makes sense. 

[alena2k · Accepted solution]

I would use calculate column, something like

Count Values = CALCULATE(COUNTA(Table1[Column B]), ALL(Table1[Column B]), Table1[Column A]= EARLIER(Table1[Column A]))

Maybe there is better way to calculate, but idea is to have column which you cna use in visual filter.

[Ashish_Mathur · Accepted solution]

Hi @jvanmeter,

Drag column A to the visual and then use this calculated field formula i.e. measure

=DISTINCTCOUNT(Data[ColumnB])

Now apply a criteria on the measure as > 100

[alena2k · Accepted solution]

I would use calculate column, something like

Count Values = CALCULATE(COUNTA(Table1[Column B]), ALL(Table1[Column B]), Table1[Column A]= EARLIER(Table1[Column A]))

Maybe there is better way to calculate, but idea is to have column which you cna use in visual filter.