cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Route217 Member
Member

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Populate Table with Values based on Lookup Table

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
2 REPLIES 2
Super User
Super User

Re: Populate Table with Values based on Lookup Table

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

Hamburg - Germany
If I answer you question, please mark my post as solution, this will also help others.
Proud to be a Datanaut!
Route217 Member
Member

Re: Populate Table with Values based on Lookup Table

tom,,,,what can i say amazing......thank you sir...