topic Re: Removing the filters on table and then filtering it based on different table's col. in DAX Commands and Tips

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

Anonymous — Thu, 12 Oct 2023 15:50:58 GMT

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

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

Dangar332 — Thu, 12 Oct 2023 17:11:48 GMT

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.

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

Anonymous — Thu, 12 Oct 2023 17:24:54 GMT

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

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

Dangar332 — Fri, 13 Oct 2023 06:48:09 GMT

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

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

Anonymous — Mon, 16 Oct 2023 20:29:16 GMT

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.