Issues with FILTER and ALLSELECTED when using filters from dimension table

[marcel97]

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:

var Previousdate = MAXX(FILTER(ALLSELECTED(Items), Items[Date_ID]<SELECTEDVALUE(Items[Date_ID])), Items[Date_ID])

Returning the Count of Items for the previous week is tricky; I tried the following measure:

Total_Count_Previousdate = SUMX(Filter(Items, Items[Date_ID]=Previousdate),Items[Total Count])

which does not yield any results, therefore I tried:

Total_Count_Previousdate = SUMX(FIlter(ALLSELECTED(Items),Items[Date_ID]=Previousdate),Items[Total Count])

which delivers the numbers that I want to see:

However; I also want those numbers to react to filters, which is only possible when I use filter attributes from this table (e.g. TEAM_ID), not attributes from a related dimension table like this one:

TEAM_ID	TEAM_NAME
1	Team 1
2	Team 2
3	Team 3

 How can I adjust the measure so that the numbers will be influenced by filters that I set on another dimension table?

Any help is appreciated!

Thanks!!!

[CoreyP]

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. 

[Anonymous · Accepted solution]

Hi @marcel97 ,

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