Forum Discussion
Helper IAverage duration between two dates is not calculated
actually when I saw your reply I had a 2nd look at the solution and the results.
In your report you are slicing by 'vw_powerBI'[stamnummer] which means that the filter context contains the table of each student and there is no need to CALCULATETABLE - ALLEXCEPT. Therefore, the VALUES ( 'vw_powerBI'[Graduated?] ) should contain all the "Yes" and "No" values for each "stamnummer". The limitation I set is: IF at least one "Yes" is available for the current student then do the calculation otherwise return blank. I see no reason why this shouldn't work.However this condition need to be applied inside the SUMX as well in order to limit the total average (In the column total cell) to only the students who have "Yes" in their Graduate status.
Study duration = IF ( "Yes" IN VALUES ( 'vw_powerBI'[Graduated?] ), AVERAGEX ( VALUES ( 'vw_powerBI'[stamnummer] ), IF ( "Yes" IN CALCULATETABLE ( VALUES ( 'vw_powerBI'[Graduated?] ) ), DATEDIFF ( CALCULATE ( MIN ( 'vw_powerBI'[registrationdate] ) ), CALCULATE ( MAX ( 'vw_powerBI'[deregistrationdate] ) ), DAY ) ) ) )
Helper IHi,
I've tried both ways, but it all gives the same result. The study duration for each student graduated or not...
Thanks,
Jo
Community ChampionPlease try
Study duration =
VAR Graduated =
CALCULATETABLE (
VALUES ( 'vw_powerBI'[Graduated?] ),
ALLEXCEPT ( 'vw_powerBI', 'vw_powerBI'[Studentnumber] )
)
RETURN
IF (
"Yes" IN Graduated,
AVERAGEX (
VALUES ( 'vw_powerBI'[studentnumber] ),
CALCULATE (
DATEDIFF (
MIN ( 'vw_powerBI'[registrationdate] ),
MAX ( 'vw_powerBI'[deregistrationdate] ),
DAY
)
)
)
)