Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have a data model that includes a User table, which includes an individual name and the team in which they belong to, that is joined to a transactional table (Owner History) by User Name. Together, they are used to show the total case load being managed across teams. The Owner History table offers a record/row every time there is an ownership change within a case. I want to build a measure that reflects the total number of unique cases that are owned both at the individual level and the team level. Here’s an example:
User Table
UserName Team
Person1 A
Person2 B
Person3 C
Person4 A
Owner History
UserName Case# Date
Person1 123 12/1/17
Person2 123 12/2/17
Person1 123 12/3/17
Person3 456 1/4/18
Person4 456 1/4/18
Person1 456 1/5/18
Person2 456 1/6/18
Person4 456 1/6/18
Person4 789 1/3/18
Person2 789 1/4/18
What I would like to produce:
Measure 1 (Distinct count of the total number of cases owned by an individual)
Person1 = 2
Person2 = 3
Person3 = 1
Person4 = 2
Measure 2 (Distinct count of the total number of cases owned by a team)
Team A = 3
Team B = 3
Team C = 1
I’ve been able to produce Measure 1 okay, but I can’t seem to get the correct filter context for Measure 2; what I get produces an additive result, so from this example it results in Team A having 4 cases, but in reality, it should only be 3, as both Person1 and Person4 owned case# 456.
Appreciate any help that is offered.
Thank you,
Solved! Go to Solution.
Hi,
Even the simple count distinct of the column case# would solve your need.
Not getting why you need a separate DAX. can you clarify my understanding ?
The Screenshot for the same is below.
Hi,
Even the simple count distinct of the column case# would solve your need.
Not getting why you need a separate DAX. can you clarify my understanding ?
The Screenshot for the same is below.
Thank you, yes there was an error in my part based on my interpretation of the results. Appreciate the help.
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |