Double relation

[EVEAdmin]

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]

@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.

[v-henryk-mstf]

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.

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]

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