Forum Discussion

Brian415_'s avatar
Brian415_
Frequent Visitor
2 years ago
Solved

Priority Rules / Column in Table Visualisation

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!

General Comment
Need Help
Show and Tell
Tips and Tricks
Tutorial Requests
  • tharunkumarRTK's avatar
    tharunkumarRTK
    2 years ago

    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

     

4 Replies

  • tharunkumarRTK's avatar
    tharunkumarRTK
    Super User
    2 years ago

    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_'s avatar
      Brian415_
      Frequent Visitor
      2 years ago

      I will try this and keep you updated. Thanks! 

    • Brian415_'s avatar
      Brian415_
      Frequent Visitor
      2 years ago

      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.