cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EVEAdmin
Helper V
Helper V

Double relation

Hi all

 

I have a query with ITEMS. 

Each item includes:

  • a category
  • a subcategory

Then, I have a query with CATEGORY, where the same field is used to identify the category or subcategory.

It is simple to create a relation from the ITEMS query to the CATEGORY query, based on the category field
However, I need to create a 2nd relation from the ITEMS query to CATEGORY query again, this time based on the SUBcategory field. 
I'm thinking to clone the CATEGORY query, especially it contains a limited number of records.

Another solution coudl be to add a calculate column to the ITEMS query. This column will be the SUBcategoryID

Else, what approach is best?

 

EVEAdmin_0-1619507271710.png

 



3 REPLIES 3
EVEAdmin
Helper V
Helper V

@Jihwan_Kim @v-henryk-mstf many thanks, appreciated
The screen shot below gives an indication of the queries and their relations.
The query T_IIInvoiceItem stores the sales records by items with category only
The query T_SIStockItem stores the item records, with category and subcategory ID's
The query T_StStockType stores the category ID's and category text. A category can be also a subcategory.
So, if I need to know the subcategory of an item, I need to contact the T_StStockType query. 
If I create a relation, based on the SiSubStockTypeID_N field, then I build a second relation. The line is obviously dotted, as it is not the active relation.
I'd rather keep away from the USERELATIONSHIP approach, for simplicity.
So, is there any alternative approach? I'm think to clone the T_STStockType query, as it includes a very limited of records, less than 100.


EVEAdmin_0-1619711859709.png

 

v-henryk-mstf
Community Support
Community Support

Hi @EVEAdmin ,

 

The first thing you need to know is that only one active relationship can be created between the two tables. If you need to continue to add a relationship, you can only create an inactive relationship. According to the creation logic given in the official document, you can modify it in conjunction with your own data model.

v-henryk-mstf_0-1619680021325.png

 

Here is the reference document link:Active vs inactive relationship guidance - Power BI | Microsoft Docs


If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.

Best Regards,
Henry

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

Jihwan_Kim
Super User
Super User

Hi, @EVEAdmin 

Please correct me if i wrongly understood your question.

You can create two relationships.

In a general situation, one will be an active relationship, and the other will be an inactive relationship.

And most of the measures will be calculated based on the active relationship.

If you want your measures to be calculated based on the inactive relationship, you can use Userelationship function inside your calculate function.

If it is OK with you, please share your sample pbix file's link with an explanation of the desired outcome. Then I can try to look into it to come up with accurate measures.

 

Thanks.

 

Hi, My name is Jihwan Kim.

 

If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

 

Linkedin: linkedin.com/in/jihwankim1975/

Twitter: twitter.com/Jihwan_JHKIM


If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

Best regards, JiHwan Kim

Linkedin: https://www.linkedin.com/in/jihwankim1975/

Twitter: https://twitter.com/Jihwan_JHKIM

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.