cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
bkennedype
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 2018-05-26_0959.pngwill 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

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

View solution in original post

3 REPLIES 3
bkennedype
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.  😞

 

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?

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


Owen Auger

Did I answer your question? Mark my post as a solution!

My Blog
Connect on Twitter
Connect on LinkedIn

Helpful resources

Announcements
August 1 episode 9_no_dates 768x460.jpg

The Power BI Community Show

Watch the playback when Priya Sathy and Charles Webb discuss Datamarts! Kelly also shares Power BI Community updates.

Power BI Dev Camp Session 24 without aka link and time 768x460.jpg

Ted's Dev Camp - July 28, 2022

Watch Session 24 of Ted's Dev Camp along with past sessions!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Top Solution Authors
Top Kudoed Authors