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 :
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.
Hi, just to add,
Here is the screenshot.
With some addition function for the aggregate trouble which I tried to use FIRSTNONBLANK function.
Anyhow, there is stll the error saying I cannot use CALCULATE .... for my new column
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.
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 :
Hope it is clear out.
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.
Hi @admin_xlsior ,
Could you please mark the proper answers as solutions?