Label each Row on a table using a Measure based on a Date column

DAX DAX Calculations
#1

Hi everyone, I have a fact table with ID, Vender, Date, and Balance. I have a measure that adds a label to each row, and the rows with the max date are labeled as “Active”, rows that intersect with approved table are “Approved”, and everything else should be “canceled”.

“Canceled” is not working, as it creates an extremely large table with all rows labeled “canceled”.

Label v2= 
VAR _maxRpDt = 
CALCULATE(
    MAX('All Records'[Upload Date]),
    ALLEXCEPT(
        'All Records',
        'Date Table'[Date]
    )
)
VAR _base = 
FILTER(
    SUMMARIZE(
        'All Records',
        'All Records'[ID]
    ), _maxRpDt
)
VAR _appbase = 
FILTER(
    SUMMARIZE(
        ApprovedTable,
        ApprovedTable[AppID]
    ),_maxRpDt
)
VAR _isActive = MAX('All Records'[Upload Date]) = _maxRpDt
VAR _isApproved = NOT(ISEMPTY(INTERSECT(_base,_appbase)))
VAR _isCanceled = 
FILTER(
    _base,
    MAX('All Records'[Upload Date]) < MAX('Date Table'[Date] )
)
RETURN 
SWITCH(
    TRUE(),
    _IsActive, "Active",
    _isApproved, "Approved",
    _isCanceled, "Canceled"
)
1 Like
#2

HI @gersch.schiff
Just a question, are you using the EDNA date table? I would recommend that you use a separate date table instead of using your date item in your fact table.

The information in the bottom is using a separate date table

Don’t forget to mark your date table as a date table.

That might your problem, within your calculation.

thanks
Keith

#3

Hi @Keith , thanks for your response!
Yes, I always use EDNA date table in all my reports.
The bottom sample is something I found online as it seemed like it could be aligned to what I’m trying to accomplish, but I’m not sure. Maybe I should remove it from this post?
And yes, the date table is marked as the date table.

#4

i don’t see any reference to your date table in your calculation above.

The only thing you can do is try it.

#6

ok, I changed the calculated column to a measure and expanded it to include the three labels I need, however, only 2 out of 3 works, “canceled” is not working:

Label v2= 
VAR _maxRpDt = 
CALCULATE(
    MAX('All Records'[Upload Date]),
    ALLEXCEPT(
        'All Records',
        'Date Table'[Date]
    )
)
VAR _base = 
FILTER(
    SUMMARIZE(
        'All Records',
        'All Records'[ID]
    ), _maxRpDt
)
VAR _appbase = 
FILTER(
    SUMMARIZE(
        ApprovedTable,
        ApprovedTable[AppID]
    ),_maxRpDt
)
VAR _isActive = MAX('All Records'[Upload Date]) = _maxRpDt
VAR _isApproved = NOT(ISEMPTY(INTERSECT(_base,_appbase)))
VAR _isCanceled = MAX('All Records'[Upload Date]) > MIN('Date Table'[Date])

RETURN 
SWITCH(
    TRUE(),
    _IsActive, "Active",
    _isApproved, "Approved",
    _isCanceled, "Canceled"
)

This seems to be working now, but something I didn’t think about was how to filter/slice based on the label?

@BolajiO , this is based on your help from an earlier post. Would love to get your input here.

#7

Hi @gersch.schiff

If you could provide a sample .pbix file it would make it easier to suggest a solution or tweak your existing calculation.

Thanks.

#8

Hi @gersch.schiff,

We notice that no response was received from you on the post above.

We are waiting for your masked demo pbix file and other supporting links and details so other users can help you in your query.

Due to your inactivity, we’ll be tagging this post as Solved.

As a suggestion, please start a new thread with the updated details and your PBIX file.

This way, our experts and other community members will have access to the most recent information and be able to offer potential solutions more easily.