Get to display Text values from a column in table using another column value from the same Table.

[Arunsaikiran]

Hi guys

1. I have created a measure which changes upon selecting a city name in slicer.

city_filt = CALCULATE(min('city-slicer (2)'[city_name]),ALLSELECTED('city-slicer (2)'))

2. I have created another measure which uses this measure 'city_filt' to give the maximum revenue based on our selection in slicer.

max_rev_month = CALCULATE(MAX(revenue[plan_revenue]),FILTER(dim_cities,dim_cities[city_name]=[city_filt]))

3. Now I am trying to display the date corresponding to this measure 'max_rev_month'  and the selected city 'city_filt' which is in the same table.

I tried

max_month =

CALCULATE(min(plan_revenue[date]),

FILTER(plan_revenue,

plan_revenue[plan_revenue] = [max_rev_month]),

FILTER(fact_plan_revenue,

plan_revenue[city_code] = [city_filt]

))

I am getting the minimum date but not the exact date matching these two measures ([max_rev_month],[city_filt]). Please guide me in this

[Arunsaikiran]

                                                    

From the above table I am trying to get the date of the row where value in plan_revenue matches

with the the selected value from the city_name.

My expected output is the date where plan_revenue value = value from measure max_rev_month 

 

[v-jialluo-msft]

Hi  @Arunsaikiran ,

You can try this:

max_rev_month = CALCULATE(MAX(revenue[plan_revenue]),FILTER(ALL(dim_cities),dim_cities[city_name]=[city_filt]))

max_month =
CALCULATE(min(plan_revenue[date]),
FILTER(ALL(plan_revenue),
plan_revenue[plan_revenue] = [max_rev_month]),
FILTER(ALL(fact_plan_revenue),
plan_revenue[city_code] = [city_filt]
))

If that's not what you need, provide sample files and expected output.

Best Regards,
Gallen Luo
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[FreemanZ]

would be good to provide some sample data.