Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
14 | |
10 | |
10 | |
9 | |
9 |
User | Count |
---|---|
20 | |
13 | |
12 | |
11 | |
8 |