Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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 |
hi, @Anonymous
try below
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
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.
No , I cannot change the dependencies due to other dependencies.
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.
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |