Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello!
I have three tables from three separate data sources. Tables 2 and 3 share a Customer Code value, which I've connected using a de-duped Table 1. I am trying to add a column in Table 3 that would count the number of Doc Date values in Table 2 that are 'less than' the Invoice Date value in Table 3. Please see screenshot below for reference. I'm trying to create the 'Doc Dates Before Invoice Date' column.
As the relation from Table 2 to 3 is many-to-many, I can't seem to get this linked up to make this calculation happen. Any insight would be much appreciated!
Thanks,
Nik
Solved! Go to Solution.
You can create a calculate column using dax below:
Doc Dates Before Invoice Date = VAR Current_Invoice_Date = Table3[Invoice Date] VAR Current_Customer_Code = Table3[Customer Code] VAR Row_Number = CALCULATE(COUNT(Table2[Customer Code]), FILTER(Table2, Table2[Customer Code] = Current_Customer_Code && Table2[Doc Date] < Current_Invoice_Date)) RETURN IF(ISBLANK(Row_Number), 0, Row_Number)
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
You can create a calculate column using dax below:
Doc Dates Before Invoice Date = VAR Current_Invoice_Date = Table3[Invoice Date] VAR Current_Customer_Code = Table3[Customer Code] VAR Row_Number = CALCULATE(COUNT(Table2[Customer Code]), FILTER(Table2, Table2[Customer Code] = Current_Customer_Code && Table2[Doc Date] < Current_Invoice_Date)) RETURN IF(ISBLANK(Row_Number), 0, Row_Number)
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-yuta-msft - Thank you so much! This just saved me so much time! This is exactly what I was looking to achieve.
User | Count |
---|---|
99 | |
87 | |
80 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |