Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DISTINCTCOUNT with filters from multiple tables.

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!

Need Help DAX measure
  • tamerj1's avatar
    tamerj1
    4 years ago

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

6 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    4 years ago

    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's avatar
      Anonymous
      Not applicable
      4 years ago

      Thank you so much tamerj1 

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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.

  • v-zhangti's avatar
    v-zhangti
    Icon for Community Support rankCommunity Support
    4 years ago

    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.