10/29/2022 0 Comments Calculate tables alternote![]() ![]() CALCULATE TABLES ALTERNOTE CODEThis was the M code that generates the matrix as a new query table (called 'Dynamic FY relation 3' table in screenshot above). I just had to ensure that when building the relation between the Project table and the Matrix Result set, the Cross Filtering is enabled in both directions. So I can at least use the data for slicers and page/report filter as needed. I also managed to create the M code to generate the result matrix as a query table in Power Query. I already tried various hard coded variations of the result matrix too, but only your layout seems to really do the trick. So the users can select them in a slicer or set them as default page/report filter. But what I really need is to use the data in the matrix as ' filters'. Although the two measures calculate well, I can only use them as ' values' in the resulting matrix visual. Thanks for the quick feedback and the PBI model. "Budget in FY19", IF ( ISBLANK ( ), "False", "True" ) "Budget in FY18", IF ( ISBLANK ( ), "False", "True" ), "Planned in FY19", IF ( ISBLANK ( ), "False", "True" ), "Planned in FY18", IF ( ISBLANK ( ), "False", "True" ), Budget18 = CALCULATE(SUM('Financial Facts'),FILTER('Financial Facts', 'Financial Facts'="Budget"&RELATED('Calendar')="FY18")) Budget19 = CALCULATE(SUM('Financial Facts'),FILTER('Financial Facts', 'Financial Facts'="Budget"&RELATED('Calendar')="FY19")) Planned18 = CALCULATE(SUM('Financial Facts'),FILTER('Financial Facts', 'Financial Facts'="Planned"&RELATED('Calendar')="FY18")) Planned19 = CALCULATE(SUM('Financial Facts'),FILTER('Financial Facts', 'Financial Facts'="Planned"&RELATED('Calendar')="FY19")) Table = Here is the sample file for your reference. ![]() Then you may get the table use SUMMARIZECOLUMNS Function. I already tried to play with the Solving Attendance with the Disconnected Table Trick from Greg Deckler.Īnd I looked at the solution provided by v-sihou-msft for the Filter by a measure problem.īoth seemed very promising to me, but I cannot get it to work for my case.Īppreciate any help to move me into the right direction.ĭesired result: Dynamically Calculated Table I'm pretty familiar with M-code, but also wanted to get better at DAX too (just a DAX beginner right now). Would performance likely be better in DAX, or M? Or is there a better way to do this in DAX, evtl. Wondering, if I rather need to go back to Power Query (M-code) and add those 4 attributes as calculated columns in a new query (PID - inPrevPlan - inPrevBudget - inCurrPlan - inCurrBudget). Those shall be used as filters for both visuals (slicers, list, matrix, charts) and report or page filters. Now, I need to create a dynamically calculated table to show per PID, whether is has any Plan/Budget in Curr/Prev FY. Planned = CALCULATE(SUM('Financial Facts'),'Financial Facts'="Planned").Budget = CALCULATE(SUM('Financial Facts'), 'Financial Facts'="Budget").In Calendar table the FYoffset =0 for the current FY and =-1 for previous FY based on todays date (our FY starts Oct, 1st).Īlso, I already have two measures defined to determine the budgeted and planned amounts. Tables Project and Financials are linked by PID, Financials and Calendar are linked by date (=last day of the month). The project table has ~ 1000 records, the Financial facts has ~ 100.000 records, the calendar table has ~3650 records (= 10 years). Financial Facts: PID - Source - Type - Date - Amount.Now I need to determine whether a specific project has any Plan or Bugdet amounts in the current or previous FY. For a project portfolio, I have Plan and Budget data broken down by FY-Month and CostType. ![]()
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |