Forum Discussion
Standard S-Curve Measure
- 4 years ago
Cracked it! Exciting DAX fun to be honest which needs some explanation as to why the stuff I sent worked in isolation but not in your solution.
Your date table is related to a number of fact tables which contain blank dates. In order to deal with that in a strong relationship DAX introduces a blank row into your date table behind the scenes. (Blank row in DAX - SQLBI)
When the date slicer is present and has a selection made the blank row is filtered out.
When the date slicer is present but it's set at the lowest value the blank row remains.
The EXP function is basically saying it doesn't know how to calculate e^BLANK which is fair enough!
First solution was to put in a page filter to remove the blank row:
Better solution is update the DAX to deal with the situation:
CM_ROW_Tax Card Baseline S-Curve = VAR k = .18 VAR a = .43 VAR MinVal = 0 VAR MaxVal = 10000 VAR StartDate = CALCULATE( MIN ( dimCalendar[Date] ), ALLSELECTED ( dimCalendar[Date] )) VAR EndDate = CALCULATE( MAX ( dimCalendar[Date] ), ALLSELECTED ( dimCalendar[Date] )) VAR CurrentDate = SELECTEDVALUE( dimCalendar[Date] ) VAR XVal = CurrentDate - StartDate VAR MaxXVal = EndDate - StartDate VAR MeanXVal = MaxXVal/2 VAR Result = IF( NOT ISBLANK(CurrentDate), MinVal + (MaxVal - MinVal) * DIVIDE ( 1, (1 + EXP(-1*k*( XVal- MeanXVal )))^a ) ) RETURN Result
In terms of the impact of the parameters I'd just based it on the formula in the blog you sent. I'll have another read and ponder on it.
bcdobbs in your file and explanation, it looks perfect. However when I copy in your formula, I get an error on the visual and it says "An argument of function 'EXP' has the wrong data type of the result is too large or too small". I'm attaching my file for you to see.
Data File
Hi,
I can make your file work by marking the date dimension as a date table and then putting a slicer on to restrict the date range.
I'm perplexed as it works up to the point where 1st Jan is in the filter context! Investigating now
- bcdobbs4 years agoCommunity Champion
Cracked it! Exciting DAX fun to be honest which needs some explanation as to why the stuff I sent worked in isolation but not in your solution.
Your date table is related to a number of fact tables which contain blank dates. In order to deal with that in a strong relationship DAX introduces a blank row into your date table behind the scenes. (Blank row in DAX - SQLBI)
When the date slicer is present and has a selection made the blank row is filtered out.
When the date slicer is present but it's set at the lowest value the blank row remains.
The EXP function is basically saying it doesn't know how to calculate e^BLANK which is fair enough!
First solution was to put in a page filter to remove the blank row:
Better solution is update the DAX to deal with the situation:
CM_ROW_Tax Card Baseline S-Curve = VAR k = .18 VAR a = .43 VAR MinVal = 0 VAR MaxVal = 10000 VAR StartDate = CALCULATE( MIN ( dimCalendar[Date] ), ALLSELECTED ( dimCalendar[Date] )) VAR EndDate = CALCULATE( MAX ( dimCalendar[Date] ), ALLSELECTED ( dimCalendar[Date] )) VAR CurrentDate = SELECTEDVALUE( dimCalendar[Date] ) VAR XVal = CurrentDate - StartDate VAR MaxXVal = EndDate - StartDate VAR MeanXVal = MaxXVal/2 VAR Result = IF( NOT ISBLANK(CurrentDate), MinVal + (MaxVal - MinVal) * DIVIDE ( 1, (1 + EXP(-1*k*( XVal- MeanXVal )))^a ) ) RETURN Result
In terms of the impact of the parameters I'd just based it on the formula in the blog you sent. I'll have another read and ponder on it.- mroberts_troy4 years agoAdvocate II
So I have this working, thanks to you! 🙂 A couple notes:
"a" is not the top slope - it's something to do with the balance between the top and bottom curves, but I can't quite nail it. If I set it at "1" then both curves change equally when I adjust "k". So just need some thinking there.
My next issue is that I need to be able to fiddle with start/end dates, because the limits of the calendar don't actually represent WHERE the curve should happen, plus if I want the date slicer to "zoom in" on a date range, then it keeps moving the S-Curve. More thinking there.
Generally though, your approach is rock solid, and very amazing. Thank you so much!