https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Get-DistinctCount-of-ColumnB-based-on-ColumnA/m-p/824262#M5728 <P>Need help to get DistinctCount of ColumnB (<STRONG>ServiceVisitYr</STRONG>) based on ColumnA (<STRONG>ServiceAddress</STRONG>)</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><STRONG>ServiceAddress</STRONG></TD><TD><STRONG>ServiceVisitYr</STRONG></TD></TR><TR><TD>A1</TD><TD>2017</TD></TR><TR><TD>A1</TD><TD>2017</TD></TR><TR><TD>A1</TD><TD>2018</TD></TR><TR><TD>A1</TD><TD>2019</TD></TR><TR><TD>A1</TD><TD>2019</TD></TR><TR><TD>A2</TD><TD>2017</TD></TR><TR><TD>A2</TD><TD>2019</TD></TR><TR><TD>A3</TD><TD>2017</TD></TR><TR><TD>A3</TD><TD>2017</TD></TR><TR><TD>A3</TD><TD>2019</TD></TR><TR><TD>A4</TD><TD>2019</TD></TR><TR><TD>A4</TD><TD>2019</TD></TR><TR><TD>A5</TD><TD>2018</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Expected Result</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>ServiceAddress</TD><TD>ServiceVisitYr</TD><TD>DistinctYrs</TD></TR><TR><TD>A1</TD><TD>2017</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>2017</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>2018</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>2019</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>2019</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>2017</TD><TD>2</TD></TR><TR><TD>A2</TD><TD>2019</TD><TD>2</TD></TR><TR><TD>A3</TD><TD>2017</TD><TD>2</TD></TR><TR><TD>A3</TD><TD>2017</TD><TD>2</TD></TR><TR><TD>A3</TD><TD>2019</TD><TD>2</TD></TR><TR><TD>A4</TD><TD>2019</TD><TD>1</TD></TR><TR><TD>A4</TD><TD>2019</TD><TD>1</TD></TR><TR><TD>A5</TD><TD>2018</TD><TD><P>1</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Thanks for your help in advance.</P> Tue, 22 Oct 2019 06:46:39 GMT Anonymous 2019-10-22T06:46:39Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Get-DistinctCount-of-ColumnB-based-on-ColumnA/m-p/824262#M5728 <P>Need help to get DistinctCount of ColumnB (<STRONG>ServiceVisitYr</STRONG>) based on ColumnA (<STRONG>ServiceAddress</STRONG>)</P><P>&nbsp;</P><TABLE><TBODY><TR><TD><STRONG>ServiceAddress</STRONG></TD><TD><STRONG>ServiceVisitYr</STRONG></TD></TR><TR><TD>A1</TD><TD>2017</TD></TR><TR><TD>A1</TD><TD>2017</TD></TR><TR><TD>A1</TD><TD>2018</TD></TR><TR><TD>A1</TD><TD>2019</TD></TR><TR><TD>A1</TD><TD>2019</TD></TR><TR><TD>A2</TD><TD>2017</TD></TR><TR><TD>A2</TD><TD>2019</TD></TR><TR><TD>A3</TD><TD>2017</TD></TR><TR><TD>A3</TD><TD>2017</TD></TR><TR><TD>A3</TD><TD>2019</TD></TR><TR><TD>A4</TD><TD>2019</TD></TR><TR><TD>A4</TD><TD>2019</TD></TR><TR><TD>A5</TD><TD>2018</TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Expected Result</P><P>&nbsp;</P><TABLE><TBODY><TR><TD>ServiceAddress</TD><TD>ServiceVisitYr</TD><TD>DistinctYrs</TD></TR><TR><TD>A1</TD><TD>2017</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>2017</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>2018</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>2019</TD><TD>3</TD></TR><TR><TD>A1</TD><TD>2019</TD><TD>3</TD></TR><TR><TD>A2</TD><TD>2017</TD><TD>2</TD></TR><TR><TD>A2</TD><TD>2019</TD><TD>2</TD></TR><TR><TD>A3</TD><TD>2017</TD><TD>2</TD></TR><TR><TD>A3</TD><TD>2017</TD><TD>2</TD></TR><TR><TD>A3</TD><TD>2019</TD><TD>2</TD></TR><TR><TD>A4</TD><TD>2019</TD><TD>1</TD></TR><TR><TD>A4</TD><TD>2019</TD><TD>1</TD></TR><TR><TD>A5</TD><TD>2018</TD><TD><P>1</P></TD></TR></TBODY></TABLE><P>&nbsp;</P><P>&nbsp;</P><P>Thanks for your help in advance.</P> Tue, 22 Oct 2019 06:46:39 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Get-DistinctCount-of-ColumnB-based-on-ColumnA/m-p/824262#M5728 Anonymous 2019-10-22T06:46:39Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Get-DistinctCount-of-ColumnB-based-on-ColumnA/m-p/824819#M5746 <DIV><DIV><SPAN>CALCULATE(DISTINCTCOUNT(Test[ServiceVisitYr]),ALLEXCEPT(Test,Test[ServiceAddress]))</SPAN></DIV></DIV> Tue, 22 Oct 2019 12:21:15 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Get-DistinctCount-of-ColumnB-based-on-ColumnA/m-p/824819#M5746 Anonymous 2019-10-22T12:21:15Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Get-DistinctCount-of-ColumnB-based-on-ColumnA/m-p/825574#M5783 <P>Thanks for your answer.</P><P>&nbsp;</P><P>Your solution works in the data set presented above.</P><P>&nbsp;</P><DIV><DIV><P>The other problem is I'm using slicer on&nbsp;</P><PRE><STRONG>ServiceVisitYr</STRONG></PRE></DIV><DIV>and when I filter data I do not get correct count</DIV></DIV> Wed, 23 Oct 2019 00:37:49 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Get-DistinctCount-of-ColumnB-based-on-ColumnA/m-p/825574#M5783 Anonymous 2019-10-23T00:37:49Z https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Get-DistinctCount-of-ColumnB-based-on-ColumnA/m-p/825722#M5790 <DIV><DIV><SPAN>Measure = CALCULATE(DISTINCTCOUNT('Table'[ServiceVisitYr]),ALLSELECTED('Table'[ServiceVisitYr]))</SPAN></DIV></DIV> Wed, 23 Oct 2019 05:11:29 GMT https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/Get-DistinctCount-of-ColumnB-based-on-ColumnA/m-p/825722#M5790 Anonymous 2019-10-23T05:11:29Z