Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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.
Solved! Go to 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
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
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.....
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
I had already tried that, but for some reason I am getting the same value for both columns (1CR, and 2CR)
User | Count |
---|---|
90 | |
84 | |
65 | |
62 | |
58 |
User | Count |
---|---|
147 | |
113 | |
95 | |
81 | |
71 |