Separate distinct count of one column for two different other columns

[Rebender]

Hi!  Newbie here!  What I am trying to accomplish is create a visual that shows a distinct count of WPS # by month for each 1CR_Associate_ID and 2CR_Associate_ID. The associate Id can be in either column so I need a different count depending on which column their ID is in.

[v-yuta-msft · Accepted solution]

Hi Rebender,

To achieve your requirement, create two calculate columns using DAX below:

Count in 1CR_Associate_ID = CALCULATE(DISTINCTCOUNT(Table1[WPS #]), FILTER(Table1, MONTH(Table1[Start Date]) = MONTH(EARLIER(Table1[Start Date])) && Table1[1CR_Associate_ID] = EARLIER(Table1[1CR_Associate_ID])))

Count in 2CR_Associate_ID = CALCULATE(DISTINCTCOUNT(Table1[WPS #]), FILTER(Table1, MONTH(Table1[Start Date]) = MONTH(EARLIER(Table1[Start Date])) && Table1[2CR_Associate_ID] = EARLIER(Table1[2CR_Associate_ID])))

 

Regards,

Jimmy Tao

[Rebender]

Thank you so much for your help!  That worked great!  I have one question....  When I add those new columns to my visual it is summing them so that I get an incorrect value.  Do you know how or which visual  I can create that will just display the value?  Here is what I am working with now.  It is a Matrix.....

[v-yuta-msft]

Hi Rebender,

Click on the new columnns, click Modeling-> Default Summarization-> Select "Don't Summarize" or in the Values field of matrix chart select "Don't summarize". Check if it can work.

Regards,

Jimmy Tao

[Rebender]

I had already tried that, but for some reason I am getting the same value for both columns (1CR, and 2CR)