Display based on the selected values from filter

[razieh1990]

Hello 

below is my data :

I have six cities and each city belongs to one region, each city has a status and each region has a status as well.

I have a slicer in Power BI and it filters the data based on "CITY"

QUESTION :

I want to create a measure to display the status of the city if I select one city only from the slicer if I choose CITY 1 and 2 from the slicer, it shows the region status which is "O" and if I choose two cities from different regions it should display blank.

here is the measure I Wrote but not working :

ss=

var count_rows= countrows(distinct(table[city])

var filtervalues= values(table[city])

return 

if(and(count_rows>1 , filtervalues in {1,2,3}, calculate(max(table[region status])),
if (and(count_rows>1 , filtervalues in {4,5,6}, calculate(max(table[region status])),calculate(max(table[region status]))))

I would appreciate it if you could help me. 

CITY	REGION	CITY STATUS	REGION STATUS
1	ont	F	O
2	ont	F	O
3	ont	O	O
4	west	O	F
5	west	F	F
6	west	F	F

[Anonymous · Accepted solution]

Hi @razieh1990 ,

The Table data is shown below:

Please follow these steps:

1.Use the following DAX expression to create a table

Table 2 = VALUES('Table'[CITY])

2.Use the following DAX expression to create a measure

Measure = 
VAR _table = SELECTCOLUMNS('Table',"Region",[REGION ])
VAR _isSameRegion = COUNTROWS(DISTINCT(_table))
VAR _city = SELECTEDVALUE('Table 2'[CITY])
RETURN 
IF(NOT ISFILTERED('Table'[CITY]),
    MAXX(FILTER('Table',[CITY] = _city),[REGION ]),
    IF(_isSameRegion = 1,
        MAXX(FILTER('Table',[CITY] = _city),[REGION ]),BLANK()
    )
)

3.Final output

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