topic Re: DAX to calculate only if X-Axis value is less than a value based on the current year in Desktop

DAX to calculate only if X-Axis value is less than a value based on the current year

LindsayK — Tue, 07 Nov 2017 18:41:58 GMT

Hello Community,

I'm a seasoned Excel user, but new to Power BI and DAX. For my current project, I have measures such as this one:

3rd Year Transfers Graduation (Cumulative) = [Count of 3rd Year Transfers Graduated (Cumulative)]/[Distinct Count of Transfers ID]

that return cumulative graduation percentage rates (for 2nd year grad rate, 3rd Year grad rate, 4th year grad rate, 5th year grad rate, 6th year grad rate) for each cohort year on the X-Axis on a column chart (see pic below):

The measure works great to provide the cumulative graduation rates for each cohort year on the X-Axis, however, for the individual cohort years on the X-Axis, we don't want to display any graduation rate for the graduation years that haven't occurred yet. For example, the cohort year 2016 on the X-Axis is showing grad rates for the 3rd year, 4th year, 5th year, and 6th year, but yet we have only just completed grad year 2017 so those grad years haven't actually occurred yet and those data representing them is misleading. I did some research and tried the following formula, but it didn't work as expected. Any ideas?

(Failed Formula) 2nd Year Transfers Graduation (Cumulative) = CALCULATE(DIVIDE([Count of 2nd Year Transfers Graduated (Cumulative)],[Distinct Count of Transfers ID]), 'Transfer_Student_Base_Dataset'[Cohort Year]<=year(TODAY()-1))

Many thanks in advance :smileyhappy:

Lindsay

Re: DAX to calculate only if X-Axis value is less than a value based on the current year

parry2k — Tue, 07 Nov 2017 18:48:09 GMT

at first look this looks wrong, seems like you want to go back to previous year:

year(TODAY()-1))

correct calculation should be

year(today())-1

Re: DAX to calculate only if X-Axis value is less than a value based on the current year

LindsayK — Tue, 07 Nov 2017 19:54:31 GMT

Thank you Parry, but not quite. The formula is changing the actual rates to incorrect values and the rates were previously correct. I just need for the rates to not show if the year isn't applicable. For one example, if we had a few students from the 2016 transfer student cohort graduate this year (their 2nd year enrolled), then we don't want their 3rd, 4th, 5th, or 6th year graduation rates to populate because that cohort's 3rd, 4th, 5th, and 6th year hasn't occurred yet. Since we are calculating cumulative rates, technically the cumulative 3rd, 4th, 5th, and 6th year graduation rates would be the same as the 2nd at this point in time, however, since the 3rd, 4th, 5th, and 6th year graduation years haven't occurred yet for the 2016 cohort, in order to avoid confusing the viewer we wouldn't want them represented with the cumulative data. Maybe I need some kind of filter or if statement instead? I can't seem to figure it out.

Thanks again,

Lindsay

Re: DAX to calculate only if X-Axis value is less than a value based on the current year

LindsayK — Tue, 07 Nov 2017 20:15:46 GMT

If it were a calculated column in an Excel table, the formula at the row level would look like this:

=IF([Cohort Year]<=YEAR(TODAY())-1,[Count of 2nd Year Graduated (Cumulative)]/[Distinct Count of ID],"")

Re: DAX to calculate only if X-Axis value is less than a value based on the current year

parry2k — Tue, 07 Nov 2017 22:46:56 GMT

try this, giving idea based on your excel formula:

myMeasure = 
var myCalculation = [Count of 2nd Year Graduated (Cumulative)]/[Distinct Count of ID]
return IF([Cohort Year]<=YEAR(TODAY())-1,myCalculation,BLANKI())

Change the name of my measure to whatever you want to call it.

Re: DAX to calculate only if X-Axis value is less than a value based on the current year

LindsayK — Wed, 08 Nov 2017 20:59:23 GMT

Thank you Parry. The formula returns an error stating that "A single value for column 'Cohort Year' in table 'Transfer_Student_Base_Dataset cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result." I did some research on this error but I can't determine what might be needed to correct it. Do you know if it's a simple fix?

Thank you,

Lindsay

Re: DAX to calculate only if X-Axis value is less than a value based on the current year

parry2k — Wed, 08 Nov 2017 21:52:30 GMT

I don' have sample data but trying to figure out, try this

myMeasure = 
var myCalculation = [Count of 2nd Year Graduated (Cumulative)]/[Distinct Count of ID]
return IF(MAX([Cohort Year])<=YEAR(TODAY())-1,myCalculation,BLANK())

I assumed these are already measure in your data model:

[Count of 2nd Year Graduated (Cumulative)]
[Distinct Count of ID]

Re: DAX to calculate only if X-Axis value is less than a value based on the current year

LindsayK — Thu, 09 Nov 2017 20:25:28 GMT

Perfect Parry! Thank you for being so patient and working through this with me. This is an enormous help to us :smileyvery-happy:

Thank you, Thank you, Thank you,

Lindsay

Re: DAX to calculate only if X-Axis value is less than a value based on the current year

parry2k — Thu, 09 Nov 2017 21:38:39 GMT

LindsayK glad to hear that I was able to help. that is what community all about. Feel free to reach out anytime. Took bit longer because was working without any sample data. :)

Cheers,