Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello folks,
I'm struggling to create a measure where I have the Number of Tenancies (cases), where the Account Class is 'REN', the In Arreas is 'Y', and the Current Former is 'C'.
The problem is that the Account Class comes from a different table and I'm unsure how to filter it.
Couldn't find anything on YT or solutions in here that could help me to solve this.
1st try.
2nd try.
Thank you!
Solved! Go to Solution.
Hi @Anonymous
you may try
No Current Arrears Cases (REN) =
CALCULATE (
DISTINCTCOUNT ( 'Account Balance Weekly'[DTE_TENANCY_NO] ),
'Account Balance Weekly'[DTE_CURRENT_FORMER] = "C",
'Account Balance Weekly'[FAB_IN_ARREARS] = "Y",
FILTER ( 'Rent Account', 'Rent Account'[Account_Class] = "REN" )
)
Hi, @Anonymous
I briefly simulated some data that I hope will fit your situation.
Measure:
No Current Arrears Cases =
CALCULATE (
DISTINCTCOUNT ( 'Account Balance Weekly'[DTE_TENANCY_NO] ),
FILTER ('Account Balance Weekly',
'Account Balance Weekly'[DTE_CURRENT_FORMER] = "C"
&& 'Account Balance Weekly'[FAB_IN_ARREARS] <> "Y"
),
FILTER ( 'Rent_Account', Rent_Account[Account Class] = "REN" )
)
Table: Rent_Account
Table: Account Balance Weekly
Shouldn't the number of cases that you want to calculate without outstanding payments not be equal to "Y"? If "Y" has other meanings, you can also calculate it as you understand it. If you want to filter multiple tables, you may need to add an additional Filter.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi, @Anonymous
Has your problem been solved, if so, please consider Accept a correct reply as the solution. If not, can you share some of the sample data for testing?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Anonymous
you may try
No Current Arrears Cases (REN) =
CALCULATE (
DISTINCTCOUNT ( 'Account Balance Weekly'[DTE_TENANCY_NO] ),
'Account Balance Weekly'[DTE_CURRENT_FORMER] = "C",
'Account Balance Weekly'[FAB_IN_ARREARS] = "Y",
FILTER ( 'Rent Account', 'Rent Account'[Account_Class] = "REN" )
)
Understood, @amitchandak
Your option also works, so could you please tell me which option is best in this case?
With FILTER function or just doing what you said above?
Thank you so much.
User | Count |
---|---|
21 | |
20 | |
15 | |
10 | |
7 |
User | Count |
---|---|
28 | |
28 | |
12 | |
12 | |
12 |