Forum Discussion

admin_xlsior's avatar
admin_xlsior
Post Prodigy
7 years ago

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.

 

Need Help

5 Replies

  • admin_xlsior's avatar
    admin_xlsior
    Post Prodigy
    7 years ago

    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

     

    Thanks

     

     

    • v-jiascu-msft's avatar
      v-jiascu-msft
      Microsoft Employee
      7 years ago

      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-tables-and-relationships 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,

      • admin_xlsior's avatar
        admin_xlsior
        Post Prodigy
        7 years ago

        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 :

         

        Hope it is clear out.

         

        Thanks,