Forum Discussion

bkennedype's avatar
bkennedype
Frequent Visitor
8 years ago
Solved

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...
Need Help
  • OwenAuger's avatar
    OwenAuger
    8 years ago

    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

bkennedype's avatar
bkennedype
Frequent Visitor
8 years ago

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's avatar
OwenAuger
Icon for Super User rankSuper User
8 years ago

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