Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
baronraghu
Helper III
Helper III

SelectedColumns measure

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

 

 

1 ACCEPTED SOLUTION
v-sihou-msft
Microsoft Employee
Microsoft Employee

@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,

View solution in original post

3 REPLIES 3
v-sihou-msft
Microsoft Employee
Microsoft Employee

@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,

Thanks @v-sihou-msft  for your reply

 

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

 

 

@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,

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.