Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started
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
Solved! Go to Solution.
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]
)
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 |
Check out the September 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
108 | |
104 | |
77 | |
35 | |
35 |
User | Count |
---|---|
158 | |
103 | |
71 | |
65 | |
53 |