topic Re: DISTINCTCOUNT with filters from multiple tables. in DAX Commands and Tips

DISTINCTCOUNT with filters from multiple tables.

Anonymous — Mon, 25 Apr 2022 15:46:27 GMT

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!

Re: DISTINCTCOUNT with filters from multiple tables.

amitchandak — Mon, 25 Apr 2022 16:24:38 GMT

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

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

Re: DISTINCTCOUNT with filters from multiple tables.

tamerj1 — Mon, 25 Apr 2022 16:27:04 GMT

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" ) )

Re: DISTINCTCOUNT with filters from multiple tables.

Anonymous — Mon, 25 Apr 2022 20:01:55 GMT

Thank you so much tamerj1

Re: DISTINCTCOUNT with filters from multiple tables.

Anonymous — Mon, 25 Apr 2022 20:08:39 GMT

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.

Re: DISTINCTCOUNT with filters from multiple tables.

v-zhangti — Thu, 28 Apr 2022 07:40:13 GMT

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.

Re: DISTINCTCOUNT with filters from multiple tables.

v-zhangti — Thu, 28 Apr 2022 08:07:13 GMT

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.