Forum Discussion
Issues with FILTER and ALLSELECTED when using filters from dimension table
Hello all,
I have a very basic calculation that I want to do; I have a table that contains for every week, how many open work items there are and how they are split between teams, types and other criteria. Please find the sample data attached:
| Date_ID | Team_ID | Type | Project | Total Count |
| 2023_12_04 | 1 | Type 1 | Project 1 | 9 |
| 2023_12_04 | 1 | Type 2 | Project 1 | 8 |
| 2023_12_04 | 2 | Type 1 | Project 1 | 6 |
| 2023_12_04 | 2 | Type 2 | Project 1 | 7 |
| 2023_12_04 | 3 | Type 1 | Project 1 | 14 |
| 2023_12_04 | 3 | Type 2 | Project 1 | 15 |
| 2023_12_01 | 1 | Type 1 | Project 1 | 7 |
| 2023_12_01 | 1 | Type 2 | Project 1 | 6 |
| 2023_12_01 | 2 | Type 1 | Project 1 | 4 |
| 2023_12_01 | 2 | Type 2 | Project 1 | 5 |
| 2023_12_01 | 3 | Type 1 | Project 1 | 12 |
| 2023_12_01 | 3 | Type 2 | Project 1 | 13 |
| 2023_11_24 | 1 | Type 1 | Project 1 | 5 |
| 2023_11_24 | 1 | Type 2 | Project 1 | 4 |
| 2023_11_24 | 2 | Type 1 | Project 1 | 2 |
| 2023_11_24 | 2 | Type 2 | Project 1 | 3 |
| 2023_11_24 | 3 | Type 1 | Project 1 | 10 |
| 2023_11_24 | 3 | Type 2 | Project 1 | 11 |
Through a measure I want to find out, how many items there have been at the previous date. Getting the Date_ID of the previous date works easily via the following variable within my measure:
Returning the Count of Items for the previous week is tricky; I tried the following measure:
| TEAM_ID | TEAM_NAME |
| 1 | Team 1 |
| 2 | Team 2 |
| 3 | Team 3 |
Hi Anonymous ,
Thanks for reaching out to us with your question. Base on my description, it seems that you want to filter the data correctly base on another dimension table. Did you create any relationship with team dimension table and Items table? If yes, it should filter the data correctly. Please find the details in the attachment.
If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with specific examples. It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
2 Replies
CoreyPSolution Sage
I would also recommend changing the data type of your date ID column to date, and creating a date dimension table. Power BI has really awesome time intelligence DAX functions that only work if you have a date table in your star schema.
Hi Anonymous ,
Thanks for reaching out to us with your question. Base on my description, it seems that you want to filter the data correctly base on another dimension table. Did you create any relationship with team dimension table and Items table? If yes, it should filter the data correctly. Please find the details in the attachment.
If the above one can't help you get the expected result, please provide some raw data in your tables (exclude sensitive data) with Text format and your expected result with specific examples. It would be helpful to find out the solution. You can refer the following link to share the required info:
How to provide sample data in the Power BI Forum
And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards