Lookup in Custom Date Measure Table

[bkennedype]

I have events in a table and I need to create a date measure table and have a way to associate a season name and game type for a specific date for a team in a league.  A visual below will help to explain.

For example, event 145 has a Jan 16, 2018 date and for NFL that date represents the 2018-19 season and it's a playoff game.  I want a measure to display that in the events table so I can use it in filters.....one for year and the other for game type.

Any help is most appreciated.

Thanks

Ben

[bkennedype]

I am trying the switch function.....

SeasonLabel = SWITCH(Group_Name,

 Date >= A && Date <= B and  Group_Name = "MLB", "Season Name 1",

 Date >= C && Date <= D and  Group_Name = "MLB", "Season Name 2",

 Date >= E && Date <= F and  Group_Name = "MLB", "Season Name 2",

 "No Season ID")

The way I read this is as follows:

I am going to switch the Group_Name for another label

when 3 conditions are met

Date is > A and < B (2 conditions) and Group_Name = X

And I simply repeat for each new label I want to use, that is each a set of 3 conditions.

Error Message:  Function 'SWITCH' does not support comparing values of type Text with values of type True/False. Consider using the VALUE or FORMAT function to convert one of the values.

It seems I'm not using the proper function.  Not a programmer.....so this just baffles me.  Could do it in Excel.  😞

[bkennedype]

Making some progress...

Season = Switch(TRUE(),

   Events2[End_Date]>=2015-01-01 && Events2[End_Date] <=2015-04-01 && Events2[Group_Name]="MLB", "2015 Pre Season",     Events2[End_Date]>=2015-01-01 && Events2[Group_Name]="NFL","NFL Season Label")

The first one does not work....it has 3 conditions....the second one works fine....it has 2 conditions.

Any ideas anyone?

[OwenAuger · Accepted solution]

Hi @bkennedype

I think regardless of the exact method, you should use a lookup table, just like in your original screenshot.

Capturing all the conditions in formulas could be quite difficult to maintain.

For instance, you could use something like the Static Segmentation pattern with DAX calculated columns

https://www.daxpatterns.com/static-segmentation/

I've dummied up a pbix containing tables similar to your original screenshot here.

PBIX link

I did notice some Events didn't match particular rows of the DateMeasures table, but I'm sure that can be fixed.

An example calculated column is:

Year Measure = 
CALCULATE (
    SELECTEDVALUE ( DateMeasures[Year], "<config error>" ),
    FILTER (
        DateMeasures,
        Events[Events Date] >= DateMeasures[Start_Date]
        && Events[Events Date] <= DateMeasures[End_Date]
        && Events[Group] = DateMeasures[Group]
    )
)

Another option is joining tables in Power Query.

Regards,

Owen