Measure returning column text value based on date

[awitt1Temp]

I have a data set of sales, items and product grouping that I have previously been able to group in this way. I have custom column written that returns a value of Core or Closeout based on when the item was ordered. So if an item was ordered on or before March 1, then it should be grouped according to the CoreGroup column and if it is after, then it should be grouped according to the CoreGroup2 column. 

RelatedCoreGroup = 
VAR SalesLineCreated = SalesTable[Sales Line Created Date] -- This is your date column in the Sales table
VAR RelatedBeforeDate = RELATED('Product Grouping'[CoreGroup]) -- Get related value from the first relationship
VAR RelatedAfterDate = RELATED('Product Grouping'[CoreGroup2]) -- Get related value from the second relationship
VAR Result = 
    IF (
        SalesLineCreated <= DATE(2025, 3, 1), -- Check if the date is on or before March 31, 2023
        RelatedBeforeDate, 
        RelatedAfterDate
    )
RETURN
    IF (
        ISBLANK(Result),
        "Other", -- If the result is blank, return 'Other'
        Result
    )

My issue now is that I am forced to Direct Query this data instead of importing so I cannot create a custom column but I still need the same functionality. Any thoughts as to how to achieve this in measures and not in custom columns? I've looked into parameters and other things but cannot get over the finish line with it. PBIX below.

Fake Data PBIX File 

[v-nmadadi-msft]

Hi @bzeeblitz 
Could you please confirm if your issue has been resolved If so, sharing the details here would be helpful for other community members who may have similar feedback.

If we don’t hear back, we’ll go ahead and close this thread. For any further discussions or questions, please start a new thread in the Microsoft Fabric Community Forum  we’ll be happy to assist.

Thank you

[v-nmadadi-msft]

Hi @awitt1Temp,

As we haven’t heard back from you, we wanted to kindly follow up to check if the solution provided by the community members for the issue worked. If our response addressed, please mark it as Accept as solution and click Yes if you found it helpful.

Thanks and regards

[v-nmadadi-msft]

Hi @awitt1Temp,

I wanted to check if you had the opportunity to review the information provided. Please feel free to contact us if you have any further questions. If our responses has addressed your query, please accept it as a solution and give a 'Kudos' so other members can easily find it.

Thank you.

[v-nmadadi-msft]

Hi @awitt1Temp,

May I ask if you have resolved this issue? If so, please mark the helpful reply and accept it as the solution. This will be helpful for other community members who have similar problems to solve it faster.

Thank you.

[DataNinja777]

Hi @awitt1Temp ,

Since you are now using DirectQuery, you cannot create calculated columns like your previous RelatedCoreGroup, but you can achieve the same result using a DAX measure. The idea is to use a measure that dynamically determines whether to show "Core" or "Closeout" based on the Sales Line Created Date. Here's the DAX measure that replicates your original logic:

RelatedCoreGroupMeasure =
VAR SalesLineCreated = MAX(SalesTable[Sales Line Created Date])
VAR RelatedBeforeDate = MAX('Product Grouping'[CoreGroup])
VAR RelatedAfterDate = MAX('Product Grouping'[CoreGroup2])
VAR Result =
    IF (
        SalesLineCreated <= DATE(2025, 3, 1),
        RelatedBeforeDate,
        RelatedAfterDate
    )
RETURN
    IF (
        ISBLANK(Result),
        "Other",
        Result
    )

You can use this measure in your visual to reflect the correct group label based on the date. However, since measures cannot be used as row groupings in matrix visuals, this approach works best if you only need to display the group label or use it in tooltips or conditional logic. If you need to use the result for grouping rows, you'll have to consider alternatives such as creating a disconnected group table or pre-aggregated calculated table, though those are more limited in DirectQuery mode. Let me know if you need help adapting the visual to support grouping with this logic.

Best regards,