create column in new table based on a conditional select in another table

[JG_Austra]

Hi - I want to create a table with all days, that looks up the Filesemester Table to return the Semester based on the DateTable.Date, which must be between Filesemester.StartDate and Filesemester.EndDate

Help please 🙂

DateTable =

ADDCOLUMNS(

    CALENDAR(DATE(2016,1,1), DATE(2024,12,31)),

    "Year", YEAR([Date]),

    "Month Number", MONTH([Date]),

    "TermofYear",

    VAR filteredTerm =

            FILTER(

                FileSemesters,

                (FileSemesters[StartDate] < [Date] &&   FileSemesters[EndDate] > [Date] ) )

       I just want the Semester column that is in the FileSemester table.  

    RETURN filteredTerm

)

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your expected result looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

In the sample, I tried to use GENERATE DAX Function to create a new table.

GENERATE function (DAX) - DAX | Microsoft Learn

DateTable = 
GENERATE (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2024, 12, 31 ) ),
        "Year", YEAR ( [Date] ),
        "Month Number", MONTH ( [Date] )
    ),
    SELECTCOLUMNS (
        FILTER (
            FileSemesters,
            ( FileSemesters[StartDate] <= [Date]
                && FileSemesters[EndDate] >= [Date] )
        ),
        "TermOfYear", FileSemesters[Semesters]
    )
)

[FreemanZ]

hi @JG_Austra ,

try like:

DateTable =

ADDCOLUMNS(

    CALENDAR(DATE(2016,1,1), DATE(2024,12,31)),

    "Year", YEAR([Date]),

    "Month Number", MONTH([Date]),

    "TermofYear",

    VAR filteredTerm =

        MAXX(

            FILTER(

                FileSemesters,

                (FileSemesters[StartDate] < [Date] &&   FileSemesters[EndDate] > [Date] ) 

            ),

             FileSemesters[Semester]
        )

    RETURN filteredTerm

)

[Kedar_Pande · Accepted solution]

@JG_Austra 

DateTable =
ADDCOLUMNS(
CALENDAR(DATE(2016, 1, 1), DATE(2024, 12, 31)),
"Year", YEAR([Date]),
"Month Number", MONTH([Date]),
"Semester",
VAR currentDate = [Date]
RETURN
MAXX(
FILTER(
FileSemesters,
FileSemesters[StartDate] <= currentDate &&
FileSemesters[EndDate] >= currentDate
),
FileSemesters[Semester]
)
)

You can then use this DateTable in your model to relate dates to semesters seamlessly.

💌 If this helped, a Kudos 👍 or Solution mark would be great! 🎉
Cheers,
Kedar
Connect on LinkedIn

[Jihwan_Kim · Accepted solution]

Hi,

I am not sure how your expected result looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file.

In the sample, I tried to use GENERATE DAX Function to create a new table.

GENERATE function (DAX) - DAX | Microsoft Learn

DateTable = 
GENERATE (
    ADDCOLUMNS (
        CALENDAR ( DATE ( 2021, 1, 1 ), DATE ( 2024, 12, 31 ) ),
        "Year", YEAR ( [Date] ),
        "Month Number", MONTH ( [Date] )
    ),
    SELECTCOLUMNS (
        FILTER (
            FileSemesters,
            ( FileSemesters[StartDate] <= [Date]
                && FileSemesters[EndDate] >= [Date] )
        ),
        "TermOfYear", FileSemesters[Semesters]
    )
)