Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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 ?
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
41 | |
19 | |
19 | |
16 | |
15 |
User | Count |
---|---|
49 | |
26 | |
22 | |
17 | |
16 |