SelectedColumns measure

[baronraghu]

Hi All,

I am working on a dashboard and would require your help in a measure.

I have slicers for the the name of companies

Wallmart

Facbook

Microsoft

Google

Apple

I have a map which shows which all countries the company has an office. At present what is happening is that when nothing is selected/all are selected the map becomes cluttered because all companies may have operations in one country.

To improve upon that I have decided to do somthing like this

when slicer is selected for a particular company then area of operations must be shown. otherwise only their head offices must be shown

Table1

Company   Area of Operation

Apple         USA

Apple         Canada

Apple         France

Google      Ireland

Google      UK

Google      Netherlands (and so on)

Table2

Company    Headoffice     Revenue in 17Q1

Apple             California          123456

Google           California          235689

Microsoft        Washington     3698

Wallmart         NewYork          36578

For this I have created a measure 
Locations = if(ISFILTERED(Company[Logo]),SELECTCOLUMNS(Assets,"Area of Operations",[Sector]),SELECTCOLUMNS(Company,"Headquaters",[HeadQuater]))

The issue i am facing is when nothing is selected/all selected then the measure give an error else it gives the result as Headquaters.

This particular measure will be shown in the map in place of Legends (which is not getting accepted in the maps visuals)

Secondly, When talking about the size of the bubble in the map visual when nothing is selected the size should vary based on revenue otherwise when a particular company is selected then the size should be fixed to 50

Size = IF(ISFILTERED(Company[Logo]),50,SELECTCOLUMNS(Company,"Revenue",[Revenue 17Q1])) (same is the case- works well for false condition but the result is not )

Error Message- Multiple values suppplied when sacalar value expected.

Look forward for your reply

Thanks

[v-sihou-msft · Accepted solution]

@baronraghu

It's not possible to dynamically populate the fields based on selection in slicer.

In this scenario, I suggest you create a hierarchy like "Head Office->Area Of Operation". I think this should be the most effective workaround, even you have to go HeadOffice level first, then drill down to detail locations.

Regards,

[baronraghu]

Thanks @v-sihou-msft  for your reply

Could you please let me know why the measure not working?

[v-sihou-msft]

@baronraghu

In your IF statement, it will return a dynamic table context, which is not supported in DAX. Please refer to links below:

Data Analysis Expressions (DAX) Overview

Row Context and Filter Context in DAX

Regards,