Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
I have a data set with customer ID, Type and enrolment date. I am trying to calculate the no of customers in a particulat category/type using a measure as some of the ID's have duplicates.
measure 1 = CALCULATE(DISTINCTCOUNT('CustomerTable'[Customer_ID]), CustomerTable[Type] = "Local")
Since I'm using many other tables I have created a relationship between the DateTable and the CustomerTable with a one to many relationship. However, on cross examining the results for measure 1, I found a higher result on a table with a 'Date' column from the DateTable. The relationship may have created more records than expected, since when I use the Enrolment date the numbers are lower and are the exact numbers.
How can I get the actual number for the measure above? I have tried adding functions such as HASONEFILTER, ALLSELECTED. I want the measure to return values only for the enrolment date when used with the DateTable.
Please help!
Sample of the data
Customer ID | Type | Date enroled |
1 | Local | 12/12/22 |
2 | International | 13/12/22 |
1 | Local | 14/12/22 |
4 | International | 15/12/22 |
1 | Local | 16/12/22 |
6 | International | 17/12/22 |
7 | Local | 18/12/22 |
8 | International | 19/12/22 |
1 | Local | 20/12/22 |
10 | International | 21/12/22 |
11 | Local | 22/12/22 |
12 | International | 23/12/22 |
13 | Local | 24/12/22 |
Solved! Go to Solution.
Hi @Anonymous
Maybe you can try this Measure.
CountCxByType =
VAR midVal_ =
CALCULATE (
MIN ( CustomerTable[Date enroled ] ),
FILTER (
ALL ( CustomerTable ),
CustomerTable[Customer ID ] = MAX ( CustomerTable[Customer ID ] )
)
)
RETURN
COUNTX (
FILTER ( CustomerTable, CustomerTable[Date enroled ] = midVal_ && CustomerTable[Type] = "Local"),
CustomerTable[Customer ID ]
)
Then, the result should look like this.
Also, attached the pbix file as reference.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
Hi @Anonymous
Maybe you can try this Measure.
CountCxByType =
VAR midVal_ =
CALCULATE (
MIN ( CustomerTable[Date enroled ] ),
FILTER (
ALL ( CustomerTable ),
CustomerTable[Customer ID ] = MAX ( CustomerTable[Customer ID ] )
)
)
RETURN
COUNTX (
FILTER ( CustomerTable, CustomerTable[Date enroled ] = midVal_ && CustomerTable[Type] = "Local"),
CustomerTable[Customer ID ]
)
Then, the result should look like this.
Also, attached the pbix file as reference.
Best Regards,
Community Support Team _ Caiyun
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let me know. Thanks a lot!
@Anonymous Perhaps:
measure 1 =
VAR __Table = SUMMARIZE('CustomerTable',[Customer ID],[Type],"__DateEnrolled",MAX('CustomerTable'[Date enrolled]))
RETURN
COUNTROWS(__Table,[Type]="Local")
@Anonymous , First Try like this
measure 1 = CALCULATE(DISTINCTCOUNT('CustomerTable'[Customer_ID]), Filter(CustomerTable, CustomerTable[Type] = "Local"))
In DISTINCTCOUNT date-wise and grand total can have differences.
Can you share what you are getting vs what is expected ?
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
11 | |
9 | |
8 | |
8 | |
7 |
User | Count |
---|---|
14 | |
12 | |
11 | |
10 | |
9 |