Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
xyz9809
Regular Visitor

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

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]])
))))

 

 

 

1 ACCEPTED SOLUTION
Ahmedx
Super User
Super User

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]))

View solution in original post

8 REPLIES 8
Ahmedx
Super User
Super User

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
Super User
Super User

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

Can you elaborate?

For example:

lbendlin_0-1700000612287.png

 

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

aj1973
Community Champion
Community Champion

Hi @xyz9809 

What is the message you receive?

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

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
Community Champion
Community Champion

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 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors