Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
There are several post about this topic but I really don't get the logic.
I have two tables feeded by two different files, so two corresponding tables.
OperationsTable :
Serial | Operation | Operation date |
1 | Injection | 02/01/2018 |
1 | Machining | 04/01/2018 |
1 | Visual control | 07/01/2018 |
2 | Injection | 03/01/2018 |
2 | Machining | 04/01/2018 |
2 | Visual control | 08/01/2018 |
3 | Injection | 03/01/2018 |
3 | Machining | 04/01/2018 |
3 | Visual control | 10/01/2018 |
NDTTable :
Serial | NDT date |
1 | 07/01/2018 |
2 | 10/01/2018 |
3 | 11/01/2018 |
The link between both serial field
In "visualisations" tab, I am doing a "table", in which I check all the fields.
I would like now to comupte in a new column litteraly : NDT date - Operation date WHERE Operation = "Injection"
But i dont even know if I should add a collumn to my "visualisation table", or create a new "data table" in which I can do this query and then call it in my "visualisation table".
Thanks a lot!
Max
Solved! Go to Solution.
I believe that this will work:
Column = DATEDIFF([NDT date],MAXX(FILTER(RELATEDTABLE(OperationsTable),[Operation]="Injection"),[Operation date]),DAY)
What this is doing:
RELATEDTABLE - brings back all related records from OperationsTable
FILTER - Filters the results from RELATEDTABLE to just those whose [Operation] is "Injection"
MAXX - Grabs the MAX out of a table, could use any aggregation
DATEDIFF takes the difference in days between the two dates
I believe that this will work:
Column = DATEDIFF([NDT date],MAXX(FILTER(RELATEDTABLE(OperationsTable),[Operation]="Injection"),[Operation date]),DAY)
What this is doing:
RELATEDTABLE - brings back all related records from OperationsTable
FILTER - Filters the results from RELATEDTABLE to just those whose [Operation] is "Injection"
MAXX - Grabs the MAX out of a table, could use any aggregation
DATEDIFF takes the difference in days between the two dates
Hey,
I have similar problem and this solution doesn't work as I am getting and error of too many arguments. I have table A with serial number and pending status on a certain date. Second table B with serial number completed status on a certain date and would like to count how many days have passed between pending and completed status.
Thanks a lot in advance.
Found out I was missing some parenthesis. The formula works just fine.
Thanks smoupre!
I undrestand a bit better the logic behind. Do you know a good tutorial about this kind of formulas?
Max
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
97 | |
84 | |
67 | |
59 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |