Forum Discussion

mateoc15's avatar
mateoc15
Icon for Advocate II rankAdvocate II
10 months ago
Solved

Multiple Dates in Cube Table - relationship to Date table

I need help with relationships betwen multiple dates in a single table and a date dimension table.  I've found some articles, but nothing quite fits exactly what I'm looking for.  I want to be able t...
Need Help
  • RonaldBalza-943's avatar
    RonaldBalza-943
    10 months ago

    Here’s the refined version of your measure:

    Unique Members by Month =
    CALCULATE(
        SUM('App Cube'[unique_users]),
        USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])
    )

     

    Unique Members by Year =
    CALCULATE(
        SUM('App Cube'[unique_users]),
        USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date])
    )


    If you want dynamic switching (Month vs Year) in one measure, use:

    Unique Members Dynamic =
    SWITCH(
        TRUE(),
        SELECTEDVALUE(Dates[Granularity]) = "Month",
            CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])),
        SELECTEDVALUE(Dates[Granularity]) = "Year",
            CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date]))
    )
RonaldBalza-943's avatar
RonaldBalza-943
Icon for Helper I rankHelper I
10 months ago

Here’s the refined version of your measure:

Unique Members by Month =
CALCULATE(
    SUM('App Cube'[unique_users]),
    USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])
)

 

Unique Members by Year =
CALCULATE(
    SUM('App Cube'[unique_users]),
    USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date])
)


If you want dynamic switching (Month vs Year) in one measure, use:

Unique Members Dynamic =
SWITCH(
    TRUE(),
    SELECTEDVALUE(Dates[Granularity]) = "Month",
        CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfMonth], Dates[Date])),
    SELECTEDVALUE(Dates[Granularity]) = "Year",
        CALCULATE(SUM('App Cube'[unique_users]), USERELATIONSHIP('App Cube'[BeginningOfYear], Dates[Date]))
)