Priority Rules / Column in Table Visualisation

[Brian415_]

Hi all, 

I am currently developing a dashboard in Power BI and encountering an issue with displaying data based on priority rules for categories. Allow me to explain my situation in detail:

Scenario: I have a table with three columns: product, price, and category. Here's a snippet of the sample data:

Product Price Category

ID_001	10.00	A
ID_001	15.00	A
ID_001	10.00	B
ID_002	25.00	B
ID_003	12.00	C

Objective: I want to display all columns in my table. However, when filtering on a specific product (e.g., ID_001), I want to prioritize the display of categories based on certain rules:

1. If Category A is present, display only Category A.
2. If both Category A and B are present, display only Category A.
3. If only Category B is present, display only Category B.
4. If both Category B and C are present, display only Category B.
5. If only Category C is present, display only Category C.

Expected Output for ID_001:

Product Price Category

ID_001	10.00	A
ID_001	15.00	A

As per the priority rules, Category B should be ignored because Category A is present.

I am seeking guidance on how to implement these priority rules effectively in Power BI.  Any insights, suggestions, or Power BI techniques to accomplish this task would be greatly appreciated.

Thank you in advance for your help!

[Ashish_Mathur]

Hi,

What is there are 3 or more categories in a product? 

[Brian415_]

It should be the same principle. 

Imagine category's A, B and C present. So priority rules:

If only A present > Return A.

If only A, B present > Return A.

The same goes for other cateogories. 

If only A, C present > Return A. 

It should always respect the order. 

[tharunkumarRTK · Accepted solution]

@Brian415_ 

Create a dedicated table with unique values of product and do not create any relation between product list table and the actual data table. Put the product column from product list into the slicer create a measure using the below pattern.

Condition_Check = 
Var SelectedProductCategories = CALCULATETABLE(VALUES('Data Table'[Category]), 'Data Table'[Product] = SELECTEDVALUE(ProductsTable[Product]),  ALLSELECTED('Data Table')) 
Var __Condition = SWITCH( TRUE(),
    COUNTROWS( EXCEPT(SelectedProductCategories, {"A"})) = 0 , "A",
    COUNTROWS( EXCEPT(SelectedProductCategories, {"A","B"})) = 0 , "A",
    COUNTROWS( EXCEPT(SelectedProductCategories, {"B"})) = 0 , "B",
    COUNTROWS( EXCEPT(SelectedProductCategories, {"B","C"})) = 0 , "B",
    COUNTROWS( EXCEPT(SelectedProductCategories, {"C"})) = 0 , "C"
)
Var result = IF(SELECTEDVALUE('Data Table'[Category]) = __Condition && SELECTEDVALUE(ProductsTable[Product]) = selectedvalue('Data Table'[Product]), 1, 0)
return result 

 You should use this meausre as a visual lelvel filter. I am attaching the file for your reference.

If the post helps please give a thumbs up

If it solves your issue, please accept it as the solution to help the other members find it more quickly.

Tharun

[Brian415_]

I will try this and keep you updated. Thanks!