Forum Discussion

ShaelynFrench's avatar
ShaelynFrench
Icon for Helper I rankHelper I
3 years ago
Solved

Create custom column with with effective dated field

Hi,

 

I have a measure that is working just fine:

ProfType = 
var _max = MAXX( FILTER( ProfessionalType, ProfessionalType[EffDate] <= MAX( Dates[Date] ) ), ProfessionalType[EffDate] )
return
Calculate(
	max( ProfessionalType[ProfessionalType] ),
	Filter( ProfessionalType,
		ProfessionalType[EffDate] = _max
		)
	)

 

The problem is that it returns a result for every Professional, even if they have no other data for a time period. So I think it would be better if this measure was a calculated column in the table instead, but I can't figure out the syntax to make that happen. Help!

  • Jihwan_Kim's avatar
    Jihwan_Kim
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but please try the below for creating a new column whether it suits your requirement.

     

    ProfType calculated column =
VAR _max =
    MAXX (
        FILTER (
            ProfessionalType,
            ProfessionalType[EffDate] <= EARLIER ( ProfessionalType[EffDate] )
                && ProfessionalType[ProfessionalType]
                    = EARLIER ( ProfessionalType[ProfessionalType] )
        ),
        ProfessionalType[EffDate]
    )
RETURN
    MAXX (
        FILTER (
            ProfessionalType,
            ProfessionalType[EffDate] = _max
                && ProfessionalType[ProfessionalType]
                    = EARLIER ( ProfessionalType[ProfessionalType] )
        ),
        ProfessionalType[ProfessionalType]
    )

1 Reply

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    3 years ago

    Hi,

    I am not sure how your datamodel looks like, but please try the below for creating a new column whether it suits your requirement.

     

    ProfType calculated column =
VAR _max =
    MAXX (
        FILTER (
            ProfessionalType,
            ProfessionalType[EffDate] <= EARLIER ( ProfessionalType[EffDate] )
                && ProfessionalType[ProfessionalType]
                    = EARLIER ( ProfessionalType[ProfessionalType] )
        ),
        ProfessionalType[EffDate]
    )
RETURN
    MAXX (
        FILTER (
            ProfessionalType,
            ProfessionalType[EffDate] = _max
                && ProfessionalType[ProfessionalType]
                    = EARLIER ( ProfessionalType[ProfessionalType] )
        ),
        ProfessionalType[ProfessionalType]
    )