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.
Hi All,
I have a problem regarding a DAX formula in a measure. I guess my problem is classic.
I have 2 tables :
- a table 'Customers', including an 'Entry date' and an 'Ending Date' of each customer
- a table 'Meetings', the meetings realized with these customers
My goal : calculate the frequency of meetings [number of meetings] / [number of days] for each customer, and calculate the global average (first part is ok but I can't find the global result)
Note: the days in contract are ok line by line, but the global calculation is wrong - so the frequency result is about 0 !
What did I do so far ?
1/ I created a Date table, linked to the meeting date + I finalized the model :
2/ I created 3 measures :
A. The number of meetings :
B. the number of days in contract :
C. The frequency :
-----------------------------
As you see in the screenshot above, there's an issue regarding the number of days in contract; this number of days looks correct line by line but the global result is wrong (61 days is of course a wrong result). That's the reason why the frequency calculation is wrong.
Here is the Pbix file : PBIX SOURCE
Many thanks in advance for your time.
Philippe
Solved! Go to Solution.
I've had a go. No guarantees but please test at your side.
I simplified the M_days_in_contract measure because I think you've got the correct context in the visual to make some of the variables simpler. You can go with your own version or this as you please.
M_Days_In_Contract =
VAR _slicer_start_Date = MIN(MyCalendar[MyDate])
VAR _slicer_end_Date = MAX(MyCalendar[MyDate])
VAR current_Start = MAX(Customers[Entry Date])
VAR _CustEndingDate = MAX(Customers[Ending Date])
VAR current_End = IF(ISBLANK(_CustEndingDate), _slicer_end_Date, _CustEndingDate)
RETURN
IF(DATEDIFF(MAX(_slicer_start_Date, current_Start), current_End,DAY) < 0,
0,
DATEDIFF(MAX(_slicer_start_Date, current_Start),current_End,DAY)+1)
// Number of days in contrat during the selected dates
and I used this in another measure
M_Days_In_ContractChilli = SUMX(VALUES(Customers[Cust ID]), Customers[M_Days_In_Contract])
Let me know what you think.
Good luck
Many thanks HotChilli.
I realized another error from my part, not in the example but in the real project : I was working on the meeting table instead of the customer table, explaining why I failed when I tried the SUMX.
Anyway, all was not clear in my head and now it's much better.
Philippe
I've had a go. No guarantees but please test at your side.
I simplified the M_days_in_contract measure because I think you've got the correct context in the visual to make some of the variables simpler. You can go with your own version or this as you please.
M_Days_In_Contract =
VAR _slicer_start_Date = MIN(MyCalendar[MyDate])
VAR _slicer_end_Date = MAX(MyCalendar[MyDate])
VAR current_Start = MAX(Customers[Entry Date])
VAR _CustEndingDate = MAX(Customers[Ending Date])
VAR current_End = IF(ISBLANK(_CustEndingDate), _slicer_end_Date, _CustEndingDate)
RETURN
IF(DATEDIFF(MAX(_slicer_start_Date, current_Start), current_End,DAY) < 0,
0,
DATEDIFF(MAX(_slicer_start_Date, current_Start),current_End,DAY)+1)
// Number of days in contrat during the selected dates
and I used this in another measure
M_Days_In_ContractChilli = SUMX(VALUES(Customers[Cust ID]), Customers[M_Days_In_Contract])
Let me know what you think.
Good luck
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 |
---|---|
49 | |
26 | |
21 | |
15 | |
12 |
User | Count |
---|---|
57 | |
49 | |
44 | |
19 | |
18 |