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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
admin_xlsior
Post Prodigy
Post Prodigy

USERELATIONSHIP

Hi Guys,

 

I'm newbie at Power BI, probably I'm gonna just shoot my curiosity.

 

As fr as I know, Power BI has limitation to create relation ship to same table or even it is indirect. 

Lets say I have 3 tables :

1. Resources

2. Company

3. Sales Fact

 

I already connect Sales Fact to Company, and Sales Fact to Resources. That way when I want to connect Resources to Company, it gave me abiguity error. Then I should make it inactive.

 

Searched on the net, I can use function USERELATIONSHIP to activate it during Report.

 

Problem encounter is, is this function cannot be used in Direct Query ? or am I doing it wrong. Purpose is I'm gonna put Resource in my slicer and because there is no connection to Company, it listed  resources for all company whereas I have another slicer, Company which I already select 1 or 2 inside.

 

So the step I alr raken is Create New Column, then put this :

--> Resource by company = CALCULATE(Resource[Resource Name], Resource[Resourcecompany] = Company[Company])

 

Some weird thing happen :

1. At some moment, I cannot even type the Company[company], as I type, it not listed in.

2. In relation to point no 1, at the time I'm able to type it and finish the formula as above, there is an error message saying my Resouce[ResourceName] need to be aggregate, but Direct query just not support aggregate for string such as FIRST()

 

There are many limitation of this Direct Query, as I tried other function such CALCULATETABLE, also not supported (unless it is Imported).

 

Any advice so I can create a list of resource in the slicer, filtered by company selected on the other slicer ?

 

Thanks in advance.

 

5 REPLIES 5
admin_xlsior
Post Prodigy
Post Prodigy

Hi, just to add,

 

Here is the screenshot. 

With some addition function for the aggregate trouble which I tried to use FIRSTNONBLANK function.

image.png

 

Anyhow, there is stll the error saying I cannot use CALCULATE .... for my new column

 

Thanks

 

 

Hi @admin_xlsior,

 

To be honest, your model needs to be optimized. Usually, the two dimensions should be independent. 

1. About the ambiguity error, please refer to /desktop-create-and-manage-relationships#adjusting-cross-filter-direction-for-a-complex-set-of-table... for an answer.

2. Can you adjust the two tables in the source?

3. If it's possible to merge the two tables?

4. Can you share a sample? Please mask the sensitive parts.

 

 

Best Regards,

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

Hi, 

Thank you for the response.

 

I'm not sure I can merge it, each table each table has it purpose.  And I understand why you are saying need to optimized, especially if you looking at my ResourceCompany Table, and I open for an advice. However just need to inform that these tables is getting directly from OLTP / Transaction database and it is the way they built it.

 

And also we using Direct Query for the fetching method, so understand there might be some function limitation inside Power BI when we create measures or column.

 

To make it clear here is some picture to describe more clear :

image.png

 

Hope it is clear out.

 

Thanks,

 

Hi @admin_xlsior ,

 

Could you please mark the proper answers as solutions?

 

 

Best Regards,

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

Hi @admin_xlsior,

 

Actually, you don't need to establish a relationship between Resource and Company. Why? If you want to analyze the Resouce and the Company, the Resource table has everything. You don't need to filter the Resource table from the Company table.

Finally, you have slicers from Company and Resouce to analyze the SalesFact table. For example, if you select BGR from the Company table and select Matt from the Resource table, the result is empty.

Please give it a try anyway.

 

 

Best Regards,

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.