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 all,
Please can someone help me with this, I've spent a week on this but my code does not work:
'''
'''
the ["max_date"] and [pid] are in red Unexpected expression
Solved! Go to Solution.
Hi @toum
If I understand it correctly and if your final expected result is a new table, you can try below DAX to create a new table.
New Table =
var __minDate = MIN('Date'[Date])
var __maxDate = MAX('Date'[Date])
var __table = SUMMARIZE(FILTER('Table','Table'[vdate]<=__maxDate && 'Table'[vdate]>= __minDate),'Table'[pid],"max_date",MAX('Table'[vdate]))
return
ADDCOLUMNS(__table,"total_qty",SUMX(FILTER('Table','Table'[pid]=[pid] && 'Table'[vdate]=[max_date]),'Table'[qty]))
If your expected result is a measure, you can try
Measure =
var __minDate = MIN('Date'[Date])
var __maxDate = MAX('Date'[Date])
var __table = SUMMARIZE(FILTER('Table','Table'[vdate]<=__maxDate && 'Table'[vdate]>= __minDate),'Table'[pid],"max_date",MAX('Table'[vdate]))
var __table2 = ADDCOLUMNS(__table,"total_qty",SUMX(FILTER('Table','Table'[pid]=[pid] && 'Table'[vdate]=[max_date]),'Table'[qty]))
return
SUMX(__table2,[total_qty])
I used SUMMARIZE instead of GroupBy. The sample file has been attached at bottom including above two examples.
If this is not what you want, can you please provide some sample data with the necessary columns VDate, pid, qty in table format as well as the expected result based on the sample data? This could help us understand the problem and provide a possible solution faster.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @toum Sorry I forgot to attach the sample file in previous reply. Please find it in this reply.
Hi @toum
If I understand it correctly and if your final expected result is a new table, you can try below DAX to create a new table.
New Table =
var __minDate = MIN('Date'[Date])
var __maxDate = MAX('Date'[Date])
var __table = SUMMARIZE(FILTER('Table','Table'[vdate]<=__maxDate && 'Table'[vdate]>= __minDate),'Table'[pid],"max_date",MAX('Table'[vdate]))
return
ADDCOLUMNS(__table,"total_qty",SUMX(FILTER('Table','Table'[pid]=[pid] && 'Table'[vdate]=[max_date]),'Table'[qty]))
If your expected result is a measure, you can try
Measure =
var __minDate = MIN('Date'[Date])
var __maxDate = MAX('Date'[Date])
var __table = SUMMARIZE(FILTER('Table','Table'[vdate]<=__maxDate && 'Table'[vdate]>= __minDate),'Table'[pid],"max_date",MAX('Table'[vdate]))
var __table2 = ADDCOLUMNS(__table,"total_qty",SUMX(FILTER('Table','Table'[pid]=[pid] && 'Table'[vdate]=[max_date]),'Table'[qty]))
return
SUMX(__table2,[total_qty])
I used SUMMARIZE instead of GroupBy. The sample file has been attached at bottom including above two examples.
If this is not what you want, can you please provide some sample data with the necessary columns VDate, pid, qty in table format as well as the expected result based on the sample data? This could help us understand the problem and provide a possible solution faster.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.
Hi @toum Sorry I forgot to attach the sample file in previous reply. Please find it in this reply.
Thank you a lot for your help
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 |
---|---|
104 | |
101 | |
79 | |
72 | |
64 |
User | Count |
---|---|
142 | |
108 | |
101 | |
81 | |
74 |