Forum Discussion
Solved
Measure changing result when switching from localhost SASS model to live server connection
Hello Community,
I have a measure that calculates how many of my vendors make up 80% of my spend. It works perfectly when I am connected through localhost (that's how I created it and tested it), but when I switch to live connection to the server (same model and same environment), that number changes.
My measure is as follows:
# of Suppliers for 80% of Spend =
VAR temp = SUMMARIZE(Vendor,Vendor[VEND_ID],"% Spend", [% of Total Amount Spend])
VAR temp1 = ADDCOLUMNS(temp,"RANK",RANKX(temp, [% Spend],,DESC,Dense))
VAR temp2 = ADDCOLUMNS(temp1, "Cumulative",SUMX(filter(temp1,[RANK] <= EARLIER([RANK])), [% Spend]))
RETURN
MINX(filter(temp2,[Cumulative]>.8),[RANK])
and
% of Total Amount Spend = DIVIDE(sum('Purchase Order'[PO_LN_PO_LN_TOT_AMT]),calculate(sum('Purchase Order'[PO_LN_PO_LN_TOT_AMT]),ALLEXCEPT('Purchase Order','Purchase Order'[PO_LN_PO_LN_TOT_AMT])))
The ecxpected result is 266. I have validated that in many ways. When I switch from localhost to live connect, the result changes to 1 😞
I know some functions will work on localhost, but not live connect, such as distinctcountnoblank... I had another measure I had to modify because of that, but all functions within my measure seems to work on the live server connection, so I can't figure out what is causing this.
Please help!
Thanks!
If I had to guess, it is EARLIER. It is not on the list of supported DAX functions for your scenario:
Try this:
VAR temp = SUMMARIZE(Vendor,Vendor[VEND_ID],"% Spend", [% of Total Amount Spend])VAR temp1 = ADDCOLUMNS(temp,"RANK",RANKX(temp, [% Spend],,DESC,Dense))VAR temp2 = ADDCOLUMNS(temp1, "Cumulative",VAR __Rank = MAX([RANK]) RETURN SUMX(filter(temp1,[RANK] <= __Rank)), [% Spend]))RETURNMINX(filter(temp2,[Cumulative]>.8),[RANK])
2 Replies
If I had to guess, it is EARLIER. It is not on the list of supported DAX functions for your scenario:
Try this:
VAR temp = SUMMARIZE(Vendor,Vendor[VEND_ID],"% Spend", [% of Total Amount Spend])VAR temp1 = ADDCOLUMNS(temp,"RANK",RANKX(temp, [% Spend],,DESC,Dense))VAR temp2 = ADDCOLUMNS(temp1, "Cumulative",VAR __Rank = MAX([RANK]) RETURN SUMX(filter(temp1,[RANK] <= __Rank)), [% Spend]))RETURNMINX(filter(temp2,[Cumulative]>.8),[RANK])thank you a bunch! It worked!!!
