Adding a Slicer by USERELATIONSHIP

[AkoFar]

Hello All,

I need to use a coulmn as a slicer from another table with inactive relashionship.

I created a coulmn with the following DAX code :

 Program Academic Year  =
CALCULATE (
    VALUES ( 'Date Calendar'[Academic Period] ),
    USERELATIONSHIP ( 'Program Hierarchy'[Program Offering Start Date], 'Date Calendar'[Calendar Date] ),
   )

It doesn't work and I see this error : A table of multiple values was supplied where a single value was expected.

So I added a filter which become :

 Program Academic Year  =
CALCULATE (
    VALUES ( 'Date Calendar'[Academic Period] ),
    USERELATIONSHIP ( 'Program Hierarchy'[Program Offering Start Date], 'Date Calendar'[Calendar Date] ),
    FILTER (
        'Date Calendar',
        'Program Hierarchy'[Program Offering Start Date] = 'Date Calendar'[Calendar Date]
    )
)

My question is why without filter my code does not work, I have a 1 to many relashionship so It shoud work without filter.

[amitchandak · Accepted solution]

@AkoFar ,It should return one value

example

Program Academic Year =
CALCULATE (
Max ( 'Date Calendar'[Academic Period] ),
USERELATIONSHIP ( 'Program Hierarchy'[Program Offering Start Date], 'Date Calendar'[Calendar Date] ),
FILTER (
'Date Calendar',
'Program Hierarchy'[Program Offering Start Date] = 'Date Calendar'[Calendar Date]
)
)