Forum Discussion

Sibrulotte's avatar
Sibrulotte
Helper IV
2 years ago
Solved

Distinct count from another table on max date

Hi, 

two tables: 

Balances:

DateAccountAmount
2024-05-27110
2024-05-27210
2024-05-27315
2024-05-27420
2024-05-27650
2024-04-3015
2024-04-3025
2024-04-30540
2024-04-30310

 

Acounts:

AccountClient
1A
2B
3C
4A
5D
6E

 

 

A client can have more than one account, pretty basic.

When I try to do a distinct count of clients for the max date (in this case may 27th) I get the 

 

 

nb_eparg_solde_max =
// define the max date
var max_Balances = filter(all(Balances), Balances[Date] = max(Balances[Date]))
 
return
calculate(distinctcount(Accounts[Clients]), max_solde)
 
 
I should be getting 4 clients but I get all 5 of them...
Need Help
  • rajendraongole1's avatar
    rajendraongole1
    2 years ago

    Hi Sibrulotte - You need to follow a structured approach to ensure that you're considering only the clients that have transactions on the maximum date without duplication

     

    Use the below measure

     

    DistinctClientCountOnMaxDate = 

    VAR MaxDateValue = [MaxDate]
    RETURN
    CALCULATE(
        DISTINCTCOUNT(Table2[Client]),
        FILTER(
            Table1,
            Table1[Date] = MaxDateValue
        )
    )
     output:
     

     

     

    can you please use the above logic and let know.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

5 Replies

  • rajendraongole1's avatar
    rajendraongole1
    Super User
    2 years ago

    Hi Sibrulotte - You need to follow a structured approach to ensure that you're considering only the clients that have transactions on the maximum date without duplication

     

    Use the below measure

     

    DistinctClientCountOnMaxDate = 

    VAR MaxDateValue = [MaxDate]
    RETURN
    CALCULATE(
        DISTINCTCOUNT(Table2[Client]),
        FILTER(
            Table1,
            Table1[Date] = MaxDateValue
        )
    )
     output:
     

     

     

    can you please use the above logic and let know.

     

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!

    • vajifdardevdatt's avatar
      vajifdardevdatt
      New Member
      2 years ago

      Please can  someone help me with what I can buy to practice for the Microsoft  Power BI exam   after two weeks I have an exam 

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    2 years ago

    Hi,

    Create a Calendar Table with a relationship (many to One and Sigle) from the Date column of the Balances table to the Date column of the Calendar Table.  In the Balances tables, write this calculated column formula to bring over the Client from the Accounts table

    Client = related(Accounts[Client])

    Write this measure

    Measure = calculate(distinctcount(Balances[Client]),datesbetween(calendar[date],max(calendar[date]),max(calendar[date])))

    Drag this measure to a card visual.

    Hope this helps.

    • vajifdardevdatt's avatar
      vajifdardevdatt
      New Member
      2 years ago

      Hi

       

      Please help me with how I prepared for the Microsoft Power BI exam.