Items used during a particular period

[JohnTwohig]

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.

Field1	Start	End	Value
Red	1	5	1
Blue	2	4	1
Green	1	3	1
Orange	3		1
Black	1		1

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

DimField1	DimField2
One	1
Two	2
Three	3
Four	4
Five	5

 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?

[v-echaithra]

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.

[techies · Accepted solution]

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

)

 

[JohnTwohig]

@techies 

That works exactly how I wanted. 

Thanks 

[Greg_Deckler]

@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]

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.