Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany

View solution in original post

2 REPLIES 2
TomMartens
Super User
Super User

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



Did I answer your question? Mark my post as a solution, this will help others!

Proud to be a Super User!
I accept Kudos 😉
Hamburg, Germany
Anonymous
Not applicable

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.