cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Lookup in Custom Date Measure Table

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

1 ACCEPTED SOLUTION
Super User

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.

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

Owen Auger
Blog
3 REPLIES 3
Frequent Visitor

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.  😞

Frequent Visitor

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?

Super User

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.

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

Owen Auger
Blog

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors