Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hi,
This one has been a real struggle for me.
I'm trying to add a new column in my finance facts table using DAX function LOOKUPVALUE, but the tricky thing is that I only want to look for values in that same table (finance facts). When I hardcode the #search value# as in the attached picture to "ST0151_1909AC_3001_2001_SEK" it works but that is of course not doable since it should be dynamic. Everytime I try to set #search value# to column IC_Match_Rec I get "A table of multiple values was supplied where a single value was expected". The values in column IC_Match_Rec are unique, so I cannot understand this?
Below works but #Search value# is hardcoded and should be referencing the column IC_Match_Rec but then "multiple values error"?
Grateful for any tips I can get 🙂
Best Regards,
Johan
Hi @JOAP
The column you need to check for unique values is IC_Match_Pay, since it's the one the filtering is being done on. Are you taking blank values into account?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Cheers
Sorry that doesn't help. In Excel a SUMIFS() or VLOOKUP() would easily fix this but I struggle in PBI Desktop.
Could maybe the SUMX() in combination with CALCULATETABLE() in PBI work?
Below didn't work:
Hi @JOAP
You could create calculated columns
Column = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]<>BLANK()))
or
Column 2 = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]=EARLIER('Table'[IC_Match_Pay])&&'Table'[IC_Match_Pay]<>BLANK()))
Or create a measure
Measure = CALCULATE(SUM('Table'[amount]),FILTER('Table','Table'[IC_Match_Pay]<>BLANK()))
I'll go back a bit and explain my problem again because I'm really after a quick and lean solution not causing any burden on finding the matching data on any report visual. I also don't want to insert any calculated columns that really aren't necessary.
I've created this measure to capture the internal stock value free from any filter applied:
User | Count |
---|---|
83 | |
77 | |
70 | |
69 | |
54 |
User | Count |
---|---|
106 | |
96 | |
90 | |
79 | |
68 |