Forum Discussion
Power BI Filter questions
Hi,
I have been working on a problem for few days and cannot get it resolved. I guess overall this may fall into the catagory of Filter. Any suggestion would be greatly appreciated.
Section 1. Description of Goals
A matrix table consists 3 columns including Product Catagory and Sub-catagory, Measure A, and Measure B. Product Catagory and Sub-catagory are inserted as of Rows, and Measures are dropped as of values. Measures A and B are running totals for every month/year. Measure A computes product cost per catagory and sub-catagory. Measure B computes product sales per catagory and sub-catagory. 4 slicer filters with 2 for "Year" and 2 for "Month", and the slicers are synced.
Section 2. Model and Data
A total of 3 tables with Table A(cost), Table B (Sales), and Table C(sub-catagory). Table A contains Account, Year, Month, Product Catagoy and Cost Amount. Table B contains Account, Year, Month, Product Catagoy and Sales Amount. Table C contains Account and Product Sub-catagoy. Table A and B are linked to Table C using Account as the key with "Many to Many".
Section 3. Problem
The problem is with filters acrossing tables. Because "Product Catagoy" from Table A is used in the first column, the running total for Measure A is correct and Measure B is not. If I use "Product Catagoy" from Table B in the matrix first column then Measure A will be incorrect. I have been playing around filtering but I am still unable to resolve it. I also tried to recreate a relationship between Table A and B but Power BI does not allow me to because of the existing relationship of Table C. Any suggestion would be helpful.
Here are DAX codes -
Measure A =
VAR _MONTH = SELECTEDVALUE(TABLE_A[MONTH]
RETURN CALCULATE (
SUM(TABLE_A[COST]),
TABLE_A[MONTH] <= _MONTH,
REMOVEFILTERS(TABLE_B)
)
Measure B =
VAR _MONTH = SELECTEDVALUE(TABLE_B[MONTH]
RETURN CALCULATE (
SUM(TABLE_B[SALES]),
TABLE_B[MONTH] <= _MONTH,
REMOVEFILTERS(TABLE_A),
VALUES(TABLE_A[CATAGORY])
)
Thanks,
Hello yliu371 ,
To fix the problem you need to have a table that has all the product categories and subcategories without duplication and link this table to both tables so you could filter using this table.
the relationship between this table and the tables A and B would be 1 to many.
check the concept of star schema Modeling https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Follow me on Linkedin
Vote For my Idea 💡
2 Replies
- Idrissshatila
Super User
Hello yliu371 ,
To fix the problem you need to have a table that has all the product categories and subcategories without duplication and link this table to both tables so you could filter using this table.
the relationship between this table and the tables A and B would be 1 to many.
check the concept of star schema Modeling https://learn.microsoft.com/en-us/power-bi/guidance/star-schema
If I answered your question, please mark my post as solution, Appreciate your Kudos 👍
Follow me on Linkedin
Vote For my Idea 💡 It worked. I created a new table and rebult the relationships between table A, B and C, and linked table A, B to the new table as suggested. Thanks you!
