Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Data
CRM ID | CRM Close Date | Task ID | Task Close Date |
1 | 12/07/2023 | 1.1 | 14/07/2023 |
1 | 12/07/2023 | 1.2 | 15/07/2023 |
1 | 12/07/2023 | 1.3 | 16/07/2023 |
2 | 2.1 | 17/07/2023 | |
2 | 2.2 | 18/07/2023 | |
2 | 2.3 | ||
3 | 3.1 | 18/07/2023 | |
3 | 3.2 | 19/07/2023 |
Need to Calcuate # Open CRM with no Open tasks
In above eg CRM id 3 is the result as its open and all tasks have close date therefore my Measure should return 1
My currect calc is as follows
# OpenCRM with no open tasks =
Calculate(
DISTINCTCOUNT(Fct_Crm[CRM_ID]),
not(isblank(Fct_Crm[Task Close Date]),
ISBLANK(Fct_Crm[CRM_det_dte])
))
Doest give me correct answer
Any help will be appreciated
I have figured out however i am unsure if this is the most optimal way
happy for someone to suggest better way
countx(
filter(
summarize(
test,
test[CRM ID],
test[CRM Close Date],
"_1",
countrows(
filter(
test,
not isblank(test[Task Close Date])
)
),
"_2",
countrows(test)
),
isblank(test[CRM Close Date]) && [_1]=[_2]
),
test[CRM ID]
)
Hi @kkoc3karan
if I understand you correctly please update your dax to :
If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly
Hi @Ritaf1983
Thanks for responding this doesnt give me the correct result .
If i add another record to the dataset it still gives me 1 . It should give me 2 as there are 2 CRMs open(close date null) where all tasks are closed ( close date populated)
CRM IDCRM Close DateTask IdTask Close Date
1 | Wednesday, 12 July 2023 | 1.1 | Friday, 14 July 2023 |
1 | Wednesday, 12 July 2023 | 1.2 | Saturday, 15 July 2023 |
1 | Wednesday, 12 July 2023 | 1.3 | Sunday, 16 July 2023 |
2 | 2.1 | Monday, 17 July 2023 | |
2 | 2.2 | Tuesday, 18 July 2023 | |
2 | 2.3 | ||
3 | 3.1 | Tuesday, 18 July 2023 | |
3 | 3.2 | Wednesday, 19 July 2023 | |
4 | 4.1 | Monday, 17 July 2023 | |
4 | 4.2 | Tuesday, 18 July 2023 |
User | Count |
---|---|
100 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |