Forum Discussion

luisccmm's avatar
luisccmm
Icon for Helper II rankHelper II
5 years ago

Dax filter by measure value

Hi,

I need to filter measure Value_rank_2000 by measure is_in_rank_2000 =1. But seems I cannot filter a measure by another measure value. Any idea how to accomplish this??

 

value_rank_2000 = CALCULATE([sum_valor_last_period],[is_in_rank_2000]=1)

sum_valor_last_period = 
var last_period= max(Mercado[Year_month])
return
 CALCULATE( sum(Mercado[Valor]), FILTER(Mercado,Mercado[Year_month]=last_period))

 

 

 

is_in_rank_2000 = if(and([rank_competitor_2]<=[max_rank],[rank_competitor_2]>=[min_rank]),1,0)

rank_competitor_2 = 
	RANKX(
		ALL(Mercado[Competidores.Grupo Empresarial]) ,
		[sum_valor_last_period],,desc,dense)

min_rank = min(Rank_2000[rank])

max_rank = max(Rank_2000[rank])

 

 

Need Help

1 Reply

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    5 years ago

    luisccmm , what are you trying to achieve here, Rank is context-based and some context needs to there to the filer. Measure filter need a context to pushed in formula or it will take from rows. 

     

     

    refer these examples

     

    Rank Top 10= CALCULATE(if([City Rank]<=10,[Sales],BLANK()) ,VALUES(Geography[City]))
    Rank Top 10 1 = sumx(VALUES(Geography[City]),if([City Rank]<=10,[Sales],BLANK()) )
    Top 10 City Rank = CALCULATE([Sales],TOPN(10,all(Geography[City]),[Sales],DESC),VALUES(Geography[City Id]))
    Rank 2nd top = sumx(filter(VALUES(Geography[City]),[City Rank]=2),[Sales] )