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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
ITManuel
Responsive Resident
Responsive Resident

How to lookup for values/texts in a table filtered by slicer

Hi all,

 

i have a project information table named "Info" such as:

InfoValuesProject

Name

XXXP1

Contract value

100P1

Budget

80P1

Name

YYYP2

Contract value

150P2

Budget

110P2

 

I'm starting to build a report in which I select the Project via a slicer and it should show analyisis related to the selected project.

The first step would be to indicate in a card the name of the selected project.

I tried with

Name = LOOKUPVALUE(Info[Values],Info[Info],"Name") and also 

Name = calculate(LOOKUPVALUE(Info[Values],Info[Info],"Name"),Info[Project])

and a slicer with Info[Project] but it is not working, it shows "A Table of multiple values was supplied where a single value was expected". 

I read that lookupvalue cannot be dynamic. 

How can I solve this?

 

Thanks and best regards

 
1 ACCEPTED SOLUTION


@ITManuel wrote:

i tried the measure your suggested, but it says the syntax is wrong. In fact I don't understand it, the part after the Lookup function is closed. It seems not to like that.

Sorry that extra closing bracket should not be in there. If you remove that it should work.

 


@ITManuel wrote:

When I pivot the table, all values which i get turn into "1" as per the images below.


That means you've probably left the aggreate function as "count". If you expand the advanced options in the pivot dialog you can change this to "Do not aggregate"

 

Attached is a file showing both the options working

 

View solution in original post

4 REPLIES 4
d_gosbell
Super User
Super User

The following should probably work

 

Name = LOOKUPVALUE(Info[Values],Info[Info],"Name"),Info[Project], SELECTEDVALUE(Info[Project]))

 

But I think a better approach would be to change the structure of your table. The existing design is not good as you are mixing data types in the Values column. If you pivoted the values out into a structure like the following you would not need to use a measure with LOOKUPVALUE, you could just drag the [Name] column onto a card

 

Project Name Contract value Budget
P1 XXX 100 80
P2 YYY 150 110

 

 

Hi d_gosbell,

 

i tried the measure your suggested, but it says the syntax is wrong. In fact I don't understand it, the part after the Lookup function is closed. It seems not to like that.

 

In any case I would prefer to structure the data better as you suggested but have difficulties there as well. 

 

When I pivot the table, all values which i get turn into "1" as per the images below.

 

Bevor pivotting.

I1.JPG

After pivotting.

I2.JPG

 

Best regards


@ITManuel wrote:

i tried the measure your suggested, but it says the syntax is wrong. In fact I don't understand it, the part after the Lookup function is closed. It seems not to like that.

Sorry that extra closing bracket should not be in there. If you remove that it should work.

 


@ITManuel wrote:

When I pivot the table, all values which i get turn into "1" as per the images below.


That means you've probably left the aggreate function as "count". If you expand the advanced options in the pivot dialog you can change this to "Do not aggregate"

 

Attached is a file showing both the options working

 

Great, both working.

 

Thank you very much. 😁

 

All the best

 

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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