Slicer as values not the key

[oliverpenney]

Hi

I have a Fact table and a Season table

The Season dimension has a Season.LastYearSeasonID (doing a calendar isn't possible I think as i have multiple concurrent seasons)

If I add slicer for Fact.SeasonID and then two measures of

LastSeason = LOOKUPVALUE (
'DimSeason'[LastYearSeasonID],
'DimSeason'[SeasonID], 'FactLine'[SeasonId]
)

and

LinesLY =
VAR LY = min('FactLine'[LastSeason])
RETURN CALCULATE (
COUNT('FactLine'[SFID]),
FILTER ( ALL('FactLine'[SeasonID]),'FactLine'[SeasonID]=LY)
)

then LinesLY works fine, all good

But here's the weird thing, if I change my slicer to DimSeason.SeasonDesc then  LinesLY is blank

What's going on here? Any suggestions of things to try are welcome

regards

ol

[oliverpenney]

many thanks, that nudged me towards using the suffix field i had in my season dimension and...

LinesLY2 = CALCULATE(COUNT('FactLine'[SFID]), FILTER(ALL('DimSeason'),DimSeason[SeasonSuffix]=max(DimSeason[SeasonSuffix]) && DimSeason[seasonyear]=max(DimSeason[seasonyear])-1))

seems to be working, though i'll have to check it

[amitchandak · Accepted solution]

@oliverpenney , not very clear.

if you have a nonstandard calendar like Year and period and Year and Season, then have a separate table for that and Create Rank for Year Season (YYYYSS) //number 

Period Rank = RANKX(all('Period'),'Period'[year period],,ASC,Dense)
This Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])))
Last Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period Rank]=max('Period'[Period Rank])-1))

This Year Period = CALCULATE(sum('order'[Qty]), FILTER(ALL('Period'),'Period'[Period]=max('Period'[Period]) && 'Period'[Year]=max('Period'[Year])-1))