DISTINCTCOUNT with filters from multiple tables.

[Anonymous]

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!

[v-zhangti]

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.

[v-zhangti]

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.

[tamerj1 · Accepted 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" )
)

[Anonymous]

Thank you so much @tamerj1 

[amitchandak]

@Anonymous , Do not use && or || between different tables, By default it is end

In the second one just give comma after "Y" and remove &&

[Anonymous]

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.