cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Bubnash
Helper I
Helper I

Power Bi | Analysis Services | Working with Mixed Model

Hello,

 

I am working with an Analysis Services Model which works on Live Connection.

 

With the December Power Bi Update we can add local tables also and make the model mixed.

 

So in my mixed model, i want to create a measure to substract 2 columns(one from live connection and one from local).

 

Live Connection Table

 

LayerLimitDeductible
5xs550000005000000
10x101000000010000000

 

Local Table

 

ClaimIncurred
15494849
22025357
374282

 

I want a measure IncurredAboveDeductible, which will substract Incurred from Deductible

ClaimIncurredLayerDeductibleIncurredAboveDeductible
154948495x55000000494849
1549484910x10100000000

 

Kindly help if possible.

 

Thank you.

 

 

@amitchandak  @daxer @parry2k @mahoneypat @AlB @lbendlin @sanalytics

1 ACCEPTED SOLUTION
RicoZhou
Community Support
Community Support

Hi @Bubnash 

You may try make change to live connection mode for Analysis Services to change mode to DirectQuery.

Blog: Using DirectQuery for Power BI datasets and Azure Analysis Services (preview)

Then you can use datasource from live connection and local at the same time.

Try to build a new calculated table.

Table = ADDCOLUMNS(GENERATE('Local Table','Live Connection'),"IncurredAboveDeductible",IF([Incurred]-[Deductible]<0,0,[Incurred]-[Deductible]))

Result is as below.

1.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

7 REPLIES 7
RicoZhou
Community Support
Community Support

Hi @Bubnash 

You may try make change to live connection mode for Analysis Services to change mode to DirectQuery.

Blog: Using DirectQuery for Power BI datasets and Azure Analysis Services (preview)

Then you can use datasource from live connection and local at the same time.

Try to build a new calculated table.

Table = ADDCOLUMNS(GENERATE('Local Table','Live Connection'),"IncurredAboveDeductible",IF([Incurred]-[Deductible]<0,0,[Incurred]-[Deductible]))

Result is as below.

1.png

Best Regards,

Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. 

 

View solution in original post

Thank you for taking the time out to try and help me with this! I will go through the blog as well.

RicoZhou
Community Support
Community Support

Hi @Bubnash 

Could you tell me if your problem has been solved? If it is, kindly Accept it as the solution. More people will benefit from it. Or you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file from your Onedrive for Business.

 

Best Regards,

Rico Zhou

lbendlin
Super User III
Super User III

Please provide more context. Are you trying to cross join the tables (which doesn't require common columns) ?

Thank you, Cross Join worked! I could compute the measure required!

 

This is my formula>>>

IncurredAboveLayerDeductible = CALCULATE (
SUMX (
FILTER (
CROSSJOIN (
DimLayer,
SUMMARIZE (
FactClaims,
FactClaims[Inflated Incurred],
FactClaims[Client Claim Reference],
FactClaims[Client LOB]
)
),
FactClaims[Inflated Incurred] > DimLayer[Deductible]
),
FactClaims[Inflated Incurred] - DimLayer[Deductible]
)
)
lbendlin
Super User III
Super User III

What have you tried so far?  How did you connect the two data models?

Hello,

 

I have connected to the live connection model. And then added a local table with a SQL Query from Azure Analysis Services.

Just tried creating relationship between my local table and one table from Live Model. Could only create Many-Many and not Many-One like i wanted.

 

Could not create a relationship between the tables i have posted in my question as there are no matching columns.

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors
Top Kudoed Authors