Forum Discussion

razieh1990's avatar
razieh1990
Icon for Helper I rankHelper I
2 years ago
Solved

Display based on the selected values from filter

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. 

CITYREGION CITY STATUS REGION STATUS 
1ontFO
2ontFO
3ontOO
4westOF
5westFF
6westFF
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.