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.
Hi,
I have a manually created Date Table called 'Date Table' and I'd like to add two manually created columns to it. One column needs to sum the 'ClaimData'[PlanPaid_AnnualTotal] amount based on the 'ClaimData'[PaidDateMMMYY] field while the other needs to sum the 'ClaimData'[PlanPaid_AnnualTotal] amount based on the 'ClaimData'[IncurredFromMMMYY] field.
Here is an example of the Date table:
And here is an example of the ClaimData table:
I need to somehow add two manually created columns to my Date Table:
1) One column [PaidDateAmount] that will sum the ClaimData[PlanPaid_AnnualTotal] field where 'Date Table'[IncurredDateSort] matches ClaimData[PaidDateMMMYY].
2) And another [IncurredDateAmount] that will sum the ClaimData[PlanPaid_AnnualTotal] field where 'Date Table'[IncurredDateSort] matches ClaimData[IncurredDateMMMYY].
Essentially, I want the result of my Date Table to look like this:
I know Date tables are not typically used like this but my project is pretty large and complex as it is. I only want to create the Date table like this so I can plot these two amounts one a chart visual for a very specific request.
Thank you! I'll be monitoring the post if you have any questions for me.
Solved! Go to Solution.
Please create those two new measures and not columns, don't require to create two columns in the DATE table.
Just drag the measures on the Charts/Visuals and date from Date table.
You can share your pbix with dummy data, and I can try to fix that. If sharing the file not possible please try above. Let me know if this works
Hello @AW1976NOVA
So your objective is to bring these two new number columns to chart/table by Date (from date table). Inspite of creating two columns in the data table, try to do below:
1). Create first measure [PaidDateAmount] as
CALCULATE(
sum(ClaimData[PlanPaid_AnnualTotal]), TREATAS (values('Date Table'[IncurredDateSort]),ClaimData[PaidDateMMMYY]))
2). Create second measure [IncurredDateAmount] as
CALCULATE(
sum(ClaimData[PlanPaid_AnnualTotal]), TREATAS (values('Date Table'[IncurredDateSort]),ClaimData[IncurredDateMMMYY]))
Thats it, now you can drag your date from the Date table and these two measures.
Note, I typed DAX code in a notepad so might be some spelling mistakes, hope this works for you.
Please let me know if above is working or if you have any further questions.
Thank you for the help so far but it isn't working. I do not have any physical relationships and need to keep it that way. Here are my two created columns:
Please create those two new measures and not columns, don't require to create two columns in the DATE table.
Just drag the measures on the Charts/Visuals and date from Date table.
You can share your pbix with dummy data, and I can try to fix that. If sharing the file not possible please try above. Let me know if this works
Thank you! I was very confused. Thank you so much.
In addition to previous comment, I guess there are no relationship between these tables, if you have already created physical relationship between the tables between these two tables in any way, in the same MEASURES you can remove the relationship first in order to get it working
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 |
---|---|
108 | |
105 | |
88 | |
74 | |
66 |
User | Count |
---|---|
126 | |
111 | |
100 | |
83 | |
71 |