Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi BI Gurus!
I have two tables, related 1 - many.
Table1, DATES Table2, Whole Numbers
06/04/2020 3
Using NewDate = SUMX('table1', 'table1[DATES] + DAY('Table2'[Whole Numbers]))
Results: 06/14/4068
Desired Result: 06/07/2020
What did I miss? I also tried DATEADD and and recieved errors.
Any help is geatly appreciated.
Solved! Go to Solution.
if I understand right.
Do you want a DATE column in TABLE1 to add values that are calculated by a measure that gets values from TABLE2?
answer: It is NOT possible directly.
DAX formulas do not allow you to combine column data with measurements sirectally without using aggregations (e.g. SUMX).
rethink how to address the problem, such as:
I see two mistakes in your problem
Hi @kwong
here a solution with parameter:
With kind regards from the town where the legend of the 'Pied Piper of Hamelin' is at home
FrankAT (Proud to be a Datanaut)
Thanks! Will this work for adding 'random' data from table2[duration]?
@kwong ,
First of all, day function will result in error as it expects date field and it receives whole number.
I created demo data with same structure.
Table1 = date table, Table2 = table with date column and whole number column.
Relationship is based on date column, 1 to many.
In Table1 i created new column: Using NewDate = CALCULATE(SUM(Table2[Whole Number]))
For each day it calculates total of column Whole number from Table2.
And then i add calculation to add this number of days to date column.
So final calculation in column is: Using NewDate = CALCULATE(SUM(Table2[Whole Number])) + Table1[Date]
This is data in Table2:
Hi, Thanks for the quick response. This looks pretty close. I think I should provide more context...
Table2[Duration] is a Measure from a column in a third table used to calculate averages of groups of distinct values two other columns in table3.
Table1[Date] Table2[Duration]
7/31/2020 0
6/4/2020 3
5/5/2020 3
6/1/2020 12
8/6/2020 9
9/8/2020 11
8/26/2020
9/2/2020 2
The desire is to add the values in Table2[Duration] to the Dates in Table1[Date]
Return: Table1 or Table2 [Projection] (blanks may be ignored)
Any ideas?
if I understand right.
Do you want a DATE column in TABLE1 to add values that are calculated by a measure that gets values from TABLE2?
answer: It is NOT possible directly.
DAX formulas do not allow you to combine column data with measurements sirectally without using aggregations (e.g. SUMX).
rethink how to address the problem, such as:
Thanks! I think I will try to create a new table from infomration from two seprate tables.
table_current and table_past
table_combined will have [date] from table_current and average_duration from table_past
hopefully this will be fairly straight forward.
any suggestions are welcome! you folks are great!
User | Count |
---|---|
140 | |
113 | |
104 | |
77 | |
65 |
User | Count |
---|---|
136 | |
118 | |
101 | |
71 | |
61 |