Why is my query working on Desktop but not on service?

[xyz9809]

I currently use a single dataset for multiple powerBI reports that are based on service. So, I have to add/edit any measure in the desktop version of the main dataset report and then publish the dataset. This refreshes the data in all reports using the dataset.

I added this measure in the desktop version and it is working fine on the desktop but when I use it on the service, it doesn't work. Can someone help?

@amitchandak @lbendlin @Greg_Deckler @Ahmedx 

Please help

Amount Proportioned =
IF(SELECTEDVALUE(Acc[accountname]) in {"A","B","C"}, SUM(loan[accname]])*SUM(exp[debt]])/CALCULATE(SUM(exp[debt]]), Acc[accountname] in {"A","B","C"}),
 IF(SELECTEDVALUE(Acc[accountname]) in {"D","E","F"}, SUM(loan[accname]])*SUM(exp[debt]])/CALCULATE(SUM(exp[debt]]), Acc[accountname] in {"D","E","F"}),
    IF(SELECTEDVALUE(Acc[accountname]) in {"G","H","I"}, SUM(loan[accname]])*SUM(exp[debt]])/CALCULATE(SUM(exp[debt]]), Acc[accountname] in {"G","H","I"}),
        IF(SELECTEDVALUE(Acc[accountname]) in {"J","K","L"}, SUM(loan[accname]])*SUM(exp[debt]])/CALCULATE(SUM(exp[debt]]), Acc[accountname] in {"J","K","L"}),
Sum(loan[accname]])
))))

[Ahmedx · Accepted solution]

pls try this

Amount Proportioned =
VAR _accountname1 = SELECTEDVALUE(Acc[accountname]) in {"A","B","C"}
VAR _accountname2 = SELECTEDVALUE(Acc[accountname]) in {"D","E","F"}
VAR _accountname3 = SELECTEDVALUE(Acc[accountname]) in {"G","H","I"}
VAR _accountname4 = SELECTEDVALUE(Acc[accountname]) in {"J","K","L"}
VAR 
SWITCH(TRUE(),
 _accountname1, DIVIDE(SUM(loan[accname]])*SUM(exp[debt]]), CALCULATE(SUM(exp[debt]]),FILTER(ALL(Acc), Acc[accountname] in {"A","B","C"}))),
_accountname2,DIVIDE( SUM(loan[accname]])*SUM(exp[debt]]),CALCULATE(SUM(exp[debt]]),FILTER(ALL(Acc), Acc[accountname] in {"D","E","F"}))),
_accountname3, DIVIDE(SUM(loan[accname]])*SUM(exp[debt]]),CALCULATE(SUM(exp[debt]]), FILTER(ALL(Acc),Acc[accountname] in {"G","H","I"}))),
_accountname4,DIVIDE( SUM(loan[accname]])*SUM(exp[debt]]),CALCULATE(SUM(exp[debt]]), FILTER(ALL(Acc),Acc[accountname] in {"J","K","L"}))),
Sum(loan[accname]))

[lbendlin]

I recommend you add an account group column.  That will make the computation much simpler.

[xyz9809]

Can you elaborate?

[lbendlin]

For example:

[xyz9809]

Oh, i do have columns like these in my table. I am not sure why it is not working on online version 

[aj1973]

Hi @xyz9809 

What is the message you receive?

[xyz9809]

I am not getting any error message. so for example in desktop version, this measure is giving me values after calculating the proportion of loan[accname]:

5
6
7
8

in web I am getting recalculated value of loan[accname]

[aj1973]

Weired,

I would recommend that you use variables in your measure and if you could replace IF by Switch.

It sounds weired also but doesn't hurt to try

Let me know