Forum Discussion
Populate Table with Values based on Lookup Table
Hi Expert
Need to find a way to populate the Master Table with three columns to show either Y, X and W based on the Record Date, using the Fiscial Period table as a look up. Using the two date columns month Start and Month End. So for explain if we take the first date in the Master Table 07th May 2018. This falls in the Y Category (based on column ROLL12_COMPLETE_PDS_IND). hence populate Y in Column Y against those dates and so on...
Test Data
Link: https://www.dropbox.com/s/s96ibzey1ind0ai/Test.pbix?dl=0
Hey,
not sure if this is what you are looking for, but I use this DAX statement to create a calculated column:
Column = var thisDate = 'Master Data'[Report Date] return MAXX( FILTER( GENERATE( SUMMARIZE( 'Fiscial Periods' ,'Fiscial Periods'[FISCAL_MON_START_DT] ,'Fiscial Periods'[FISCAL_MON_END_DT] ,'Fiscial Periods'[ROLL12_COMPLETE_PDS_IND] ) ,var startDate = 'Fiscial Periods'[FISCAL_MON_START_DT] var endDate = 'Fiscial Periods'[FISCAL_MON_END_DT] return GENERATESERIES(startDate,endDate ,1) ) ,''[Value] = thisDate ) ,'Fiscial Periods'[ROLL12_COMPLETE_PDS_IND] )What's going on:
- The "report date" of the current row is stored in the variable thisDate
- The startdate and enddate are used inside GENERATESERIES to pivot the dates into a column automatically called value
- The table is filtered by the report date of the current row of the master table
- MAXX is used as a table is used
Hopefully, this is what you are looking for.
Regards,
Tom
2 Replies
Hey,
not sure if this is what you are looking for, but I use this DAX statement to create a calculated column:
Column = var thisDate = 'Master Data'[Report Date] return MAXX( FILTER( GENERATE( SUMMARIZE( 'Fiscial Periods' ,'Fiscial Periods'[FISCAL_MON_START_DT] ,'Fiscial Periods'[FISCAL_MON_END_DT] ,'Fiscial Periods'[ROLL12_COMPLETE_PDS_IND] ) ,var startDate = 'Fiscial Periods'[FISCAL_MON_START_DT] var endDate = 'Fiscial Periods'[FISCAL_MON_END_DT] return GENERATESERIES(startDate,endDate ,1) ) ,''[Value] = thisDate ) ,'Fiscial Periods'[ROLL12_COMPLETE_PDS_IND] )What's going on:
- The "report date" of the current row is stored in the variable thisDate
- The startdate and enddate are used inside GENERATESERIES to pivot the dates into a column automatically called value
- The table is filtered by the report date of the current row of the master table
- MAXX is used as a table is used
Hopefully, this is what you are looking for.
Regards,
Tom