Need Help with Advanced Filtering Logic in Power BI Data Model

[Anonymous]

Hello everyone,

I'm working on a Power BI report and facing a challenge with implementing dynamic filter functionality. Here's a high-level overview of my data model and the issue:

**Data Model Overview:**

1. **Transactions Table**:

  - Granularity: Individual transaction records.

  - Key Columns: TransactionDate (Year, Month, Day), PartnerID, EntityIDHashKey (represents unique entities), TransactionHashKey, RevenueAmount.

 - A PartnerID can have more than 1 EntityIDHashKey  under it.

Year	Month	Day	PartnerID	EntityHashKey	TransactionHashKey	RevenueAmount
2024	3	2	100	200	300	25.27
2024	3	2	100	201	301	183.2
2024	3	3	102	200	302	173.13
2024	3	3	103	201	303	13.8

1. **EntityAttributes Table**:

  - Granularity: Attribute level of entities.

  - Key Columns: EntityID, EntityIDHashKey, AttributeGroupName, AttributeCategory.

  - Each Entity can have 1 AttributeGroupName which falls into only 1 AttributeCategory or can have more than 1 AttributeGroupName which can fall into more than 1 AttributeCategory.

EntityHashKey	AttributeGroupName	AttributeCategory
200	AMCGroup1	AMC Category
200	NXTGroup2	AMC Category
201	YLMGroup1	AMC Category
201	UxYTGroup2	UYT Category
202	POPGroup1	POP Category
203	UYTGroup7	UYT Category

1. **Entities Table**:

  - Granularity: Each unique entity.

  - Key Columns: EntityID, EntityHashKey, PartnerID.

Relationships:

EntityAttributes  table Joins with Entities Table on EntityIDHashKey (Many to One)

Entities Table joins with Transactions Table on EntityIDHashKey (One to Many)

**Current Blocker/Requirement:**

We have `AttributeCategory` filter in the report. The data model involves complex relationships where an entity can be linked to multiple attributes or attribute groups. The filter should support both exclusive and inclusive filtering:

- **Exclusive Filtering**: When a single `AttributeCategory` is selected, the report should show data related to only that category, excluding entities associated with other categories, even if they are part of the selected category.
Ex: If user selects "AMC Category", the report should filter data only for EntityHashKey - 200, and excluding EntityHashKey 203 because it has both "AMC Category" and "UYT Category".

- **Inclusive Filtering**: When multiple `AttributeCategories` are selected, the report should reflect data for entities associated exclusively with those selected categories.
Ex: If user selects both "AMC Category", and "UYT Category", the report should filter data only for EntityHashKey - 201. The filtering mechanism should exclude others because they have other categories as well.

**Additional Considerations:**

- We are using a semantic model for the Power BI dataset; hence calculated columns or calculated tables, and measure-based filters are not feasible.

Any guidance or advice on how to approach this problem would be greatly appreciated. Thank you!

[Anonymous · Accepted solution]

Hi @Anonymous 

You can create a measure as a filter field to achieve the desired result. I made a test with below measure. It works in a report which live connects to a semantic model. 

Measure = 
VAR _curEntity = SELECTEDVALUE('Transactions Table'[EntityHashKey])
VAR _selectCats = CALCULATETABLE(VALUES('EntityAttributes Table'[AttributeCategory]),ALLSELECTED('EntityAttributes Table'))
VAR _curCats = CALCULATETABLE(VALUES('EntityAttributes Table'[AttributeCategory]),ALL('EntityAttributes Table'),'EntityAttributes Table'[EntityHashKey] = _curEntity)
VAR _commonCats = INTERSECT(_curCats,_selectCats)
RETURN
IF(COUNTROWS(_commonCats)=COUNTROWS(_selectCats)&&COUNTROWS(_commonCats)=COUNTROWS(_curCats),1,0)

Hope this would be helpful. 

Best Regards,
Jing
If this post helps, please Accept it as Solution to help other members find it. Appreciate your Kudos!

[Anonymous]

Thank you Jing !!