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 summarized table but I want to filter to the current year. How to I amend the code below to make that happen?
Order Profile = SUMMARIZE ( 'Sales Table', 'Sales Table'[Order_Num_Key], Customer[Sector], "Total Value", SUM ( 'Sales Table'[Net Invoice Value] ), "Order Count", DISTINCTCOUNT('Sales Table'[Order_Num_Key]) )
Thanks 🙂
Solved! Go to Solution.
Hi @davehus,
Try something like below please. "DimDate" is a date table.
Order Profile = CALCULATETABLE ( SUMMARIZE ( 'Sales Table', 'Sales Table'[Order_Num_Key], Customer[Sector], "Total Value", SUM ( 'Sales Table'[Net Invoice Value] ), "Order Count", DISTINCTCOUNT ( 'Sales Table'[Order_Num_Key] ) ), YEAR ( DimDate[Datekey] ) = YEAR ( TODAY () ) )
Best Regards,
Dale
Hi All,
What if I want to filter the summarized table using one of the aggregated columns?
@Anonymous can you provide an example of what you saying?
Proud to be a Super User!
Hi @davehus,
Try something like below please. "DimDate" is a date table.
Order Profile = CALCULATETABLE ( SUMMARIZE ( 'Sales Table', 'Sales Table'[Order_Num_Key], Customer[Sector], "Total Value", SUM ( 'Sales Table'[Net Invoice Value] ), "Order Count", DISTINCTCOUNT ( 'Sales Table'[Order_Num_Key] ) ), YEAR ( DimDate[Datekey] ) = YEAR ( TODAY () ) )
Best Regards,
Dale
Pleae can you explain the solution ? why we can't use dimate filter within the SUMMARIZE funciton.
@davehus don't know if this helps
https://blogs.msdn.microsoft.com/karang/2013/02/15/dax-using-filter-and-summarize-in-same-query/
Proud to be a Super User!
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 |
---|---|
113 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |