Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Solved! Go to Solution.
Hi @ahumke ,
Make a little modification to above DAX formula.
ProgramSummary = ADDCOLUMNS ( GROUPBY ( 'SummaryKPI', 'SummaryKPI'[Area], 'SummaryKPI'[Program Title], 'SummaryKPI'[KPI Metric], "MAXYear", MAXX ( CURRENTGROUP (), 'SummaryKPI'[JanuaryDate] ) ), "Score", LOOKUPVALUE ( SummaryKPI[KPI Weighted Score Component], SummaryKPI[Area], [Area], SummaryKPI[Program Title], [Program Title], SummaryKPI[KPI Metric], [KPI Metric], SummaryKPI[JanuaryDate].[Date], [MAXYear] ) )
Best regards,
Yuliana Gu
Hi @ahumke ,
Make a little modification to above DAX formula.
ProgramSummary = ADDCOLUMNS ( GROUPBY ( 'SummaryKPI', 'SummaryKPI'[Area], 'SummaryKPI'[Program Title], 'SummaryKPI'[KPI Metric], "MAXYear", MAXX ( CURRENTGROUP (), 'SummaryKPI'[JanuaryDate] ) ), "Score", LOOKUPVALUE ( SummaryKPI[KPI Weighted Score Component], SummaryKPI[Area], [Area], SummaryKPI[Program Title], [Program Title], SummaryKPI[KPI Metric], [KPI Metric], SummaryKPI[JanuaryDate].[Date], [MAXYear] ) )
Best regards,
Yuliana Gu
Thank you! that worked perfectly!
I'm not an expert in the GROUPBY function but it does look like any reference to CURRENTGROUP relates to the group created before you start using the CURRENTGROUP() function. There may be a way to say 'get me the field next to the max [January Date]' but I don't know how to do that.
However, it looks like you could create a calculated column and use LOOKUPVALUE e.g.
Column1 = LOOKUPVALUE(SummaryKPI[KPI Weighted Score Component], SummaryKPI[Area], ProgramSummary[SummaryKPI_Area], SummaryKPI[Program Title], ProgramSummary[SummaryKPI_Program Title], SummaryKPI[KPI Metric], ProgramSummary[SummaryKPI_KPI Metric], SummaryKPI[JanuaryDate], ProgramSummary[MAXYear] )
I am getting an error on that formula that "A single value for column 'summaryKPI_Area' cannot be determined. While using that code...
OK, you are going to have to debug this.
I used the data you provided. I used 'Get Data' to pull the data from Excel. I ran the DAX to create the ProgramSummary table. I then created the new column (with the LOOKUPVALUE code) in the ProgramSummary table.
So at your side, something different is happening. Let me know what you find.
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
82 | |
64 | |
62 | |
56 |
User | Count |
---|---|
171 | |
112 | |
110 | |
72 | |
72 |