Forum Discussion
Distinct Count based on 3 different conditions combined
Hello,
I am trying to write a DAX expression to take the list of IDs based on 3 different conditions and finally show the distinct count of IDs from these 3 lists put together. An ID can satisfy more than 1 condition (An ID can satisfy Condition 1 & 2, but should be counted once), hence it's required to take the disctinct count of IDs combing the below 3 conditions. How can i achieve this?
Condition 1: All IDs which were closed during the selected Month-Year
STATUS-={'Cancel','Rejected'}, Year(Closed_Date)=Max(Year), Month(Closed_Date)=Max(Month)
Condition 2: All IDs whose due date was less than or equal to selected Month-Year
STATUS-={'Cancel','Rejected'}, RSTATUS={'R'}, DueDate = {'<$(=(Max(Date)+1))'} >
Condition 3: All IDs with Due Date in the selected Month-Year and Closed_Date in or after Selected Month-Year
STATUS-={'Cancel','Rejected'}, DueDate = {'>=$(=num(MonthStart(Max(Date))))<$(=(Max(Date)+1))'}, Closed_Date -= {'<$(=num(MonthStart(Max(Date))))'}
3 Replies
- amitchandak
Super User
Anonymous , Assuming you have an independent date tbale with Month year. To select month year.
Try a measure like
measure =
VAR _max = MAXX(allselected('Date1'),'Date1' [Date])
var _min = MinX(allselected('Date1'),'Date1' [Date])return
distint(union(
Summarize(filter(Table, STATUS-={"Cancel","Rejected"}, Table[Closed_Date] >=_min && Table[Closed_Date] <=_max), Table[ID]) ,
Summarize(filter(Table, STATUS-={"Cancel","Rejected"}, Table[due_date] <=_min), Table[ID]) ,
Summarize(filter(Table, STATUS-={"Cancel","Rejected"}, Table[due_date] >=_min && Table[due_date] <=_max && Table[Closed_Date] >_max), Table[ID])
))Hi amitchandak ,
Sorry for the delay in responding back. My datamodel is way complex, so was trying to figure out how i can use your formula. I got 1 step closer, however, as you can see in the formula we are using 2 dates and in my datamodel both these dates are connected to the calendar table with dotted line relationship as shown in below image. Your formula works fine for me, but the Calendar filter is not working for this formula.
Anonymous
For now, amitchandak 's method is should work and is worth to try. For exact solution, could you please provide a sample with expected output.
Paul Zheng _ Community Support Team
