Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hi all,
i have a project information table named "Info" such as:
Info | Values | Project |
Name | XXX | P1 |
Contract value | 100 | P1 |
Budget | 80 | P1 |
Name | YYY | P2 |
Contract value | 150 | P2 |
Budget | 110 | P2 |
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
Solved! Go to 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
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.
After pivotting.
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |