Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
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:
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!
Solved! Go to 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
Hi,
What is there are 3 or more categories in a product?
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.
@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
I will try this and keep you updated. Thanks!
User | Count |
---|---|
84 | |
80 | |
72 | |
71 | |
55 |
User | Count |
---|---|
108 | |
106 | |
93 | |
84 | |
66 |