Forum Discussion

JohnTwohig's avatar
JohnTwohig
New Member
1 year ago
Solved

Items used during a particular period

I have a fact table like below that shows a colour and when it started and ended. I want to pick a period and get a list of all colours used in that period. So Red began being used in period 1 and was used until period 5. A blank End means it is still being used.

Field1StartEndValue
Red151
Blue241
Green131
Orange3 1
Black1 1

 

My dimension table has an active relationship from DimField2 to Start and an inactive relationship to End.

DimField1DimField2
One1
Two2
Three3
Four4
Five5

 I want to be able to choose a DimField1 and only see records used in that period. So, if I choose Four, I would get Red, Blue, Orange, Black.

I have a measure that gives me how many were used in each period but I can't figure out how to get which ones they are. Any suggestions?

  • techies's avatar
    techies
    1 year ago

    Hi JohnTwohig please try this

     

    IsActiveInPeriod =
    VAR SelectedPeriod =
        SELECTEDVALUE ( DimTable[DimField2] )
    VAR StartValue =
        CALCULATE ( MIN ( Fact[Start] ), ALL ( DimTable ) )
    VAR EndValue =
        CALCULATE ( MIN ( Fact[End] ), ALL ( DimTable ) )
    RETURN
    IF (
        NOT ISBLANK ( SelectedPeriod ) &&
        StartValue <= SelectedPeriod &&
        ( ISBLANK ( EndValue ) || EndValue >= SelectedPeriod ),
        1,
        0
    )
     

     

     

     

5 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    1 year ago

    JohnTwohig Remove your relationships and use this measure:

    Measure = 
    VAR __DimField2 = MAX( 'DimTable'[DimField2] )
    VAR __Table = 
        FILTER( 
            ADDCOLUMNS(
                ALL( 'Table' ), 
                "__End", IF( [End] = BLANK(), 5, [End] )
            ),
            [Start] <= __DimField2 && [__End] >= __DimField2 
        )
    VAR __Result = CONCATENATEX( __Table, [Field1], ", " )
RETURN
    __Result
    • JohnTwohig's avatar
      JohnTwohig
      New Member
      1 year ago

      Thanks for the suggestion. It didn't quite give what I wanted because I couldn't filter out the unwanted rows but The result was interesting and will probably be useful in a slightly different use case.

  • techies's avatar
    techies
    Icon for Super User rankSuper User
    1 year ago

    Hi JohnTwohig please try this

     

    IsActiveInPeriod =
    VAR SelectedPeriod =
        SELECTEDVALUE ( DimTable[DimField2] )
    VAR StartValue =
        CALCULATE ( MIN ( Fact[Start] ), ALL ( DimTable ) )
    VAR EndValue =
        CALCULATE ( MIN ( Fact[End] ), ALL ( DimTable ) )
    RETURN
    IF (
        NOT ISBLANK ( SelectedPeriod ) &&
        StartValue <= SelectedPeriod &&
        ( ISBLANK ( EndValue ) || EndValue >= SelectedPeriod ),
        1,
        0
    )
     

     

     

     

  • v-echaithra's avatar
    v-echaithra
    Icon for Community Support rankCommunity Support
    1 year ago

    Hi @JohnTwohig,

    Thank you techies , Greg_Deckler for your inputs.

    We’d like to follow up regarding the recent concern. Kindly confirm whether the issue has been resolved, or if further assistance is still required. We are available to support you and are committed to helping you reach a resolution.

    Thank you for your patience and look forward to hearing from you.

    Best Regards,
    Chaithra E.