Removing the filters on table and then filtering it based on different table's col.

[Anonymous]

I have 3 tables as below, Table1 is connected to table2 with bidirectional filtering and Table2 is connected to Datedim.

Now , I want to count and  filter like below from table1:

Count Table1[Aptid] where Table1[Case_type] = "u" and datedim[Year] = 2023

Count of "u" should be 3 as in 2023 there are 3 "u".

I am using below query which returns count as 2 as table 2 has only 2 rows of "u"

table1_Case_Created_USD =
Var Year1 = if(MONTH(TODAY()) >9 ,"FY" & RIGHT(YEAR(TODAY()),2)+1,"FY" & RIGHT(YEAR(TODAY()),2))

VAR Count1 =CALCULATE(DISTINCTCOUNT(table1[CASE_ID_Primary]),table1[New_Case_type] ="USD - Unauthorized Software Detected",DateDim[year] = Year1)
RETURN
IF(ISBLANK(count1),0,Count1)

Table1
aptid	case_type	date
1	u	10/11/2023
2	u	10/12/2023
3	u	10/13/2023
4	u	10/14/2024
5	a	10/15/2023
6	a	10/16/2024

Table2
aptid	date
1	10/11/2023
2	10/12/2023
5	10/15/2023

datedim
date	year
10/11/2023	2023
10/12/2023	2023
10/13/2023	2023
10/14/2023	2024
10/15/2023	2023
10/16/2023	2023
10/16/2024	2024

[Dangar332]

hi, @Anonymous 

try below 

coun = CALCULATE(COUNT(Table1[aptid]),

                        Table1[case_type]="u",

                         ALLCROSSFILTERED(datedim),

                        TREATAS(VALUES(datedim[date]),Table1[date])

                        )

i am upload pbix. file refer here 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi @Danger332, I am creating a calculated field for Year till date (YTD) therefore I cannot use the filter outside the calculated field. So "datedim = Year1" should be used in the calculation.

[Anonymous]

No , I cannot change the dependencies due to other dependencies.

[Dangar332]

hi, @Anonymous 
is there any chance to adjust relationship bw your table?

if yes then make your data model like below 

it give your desired output.