Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I am trying to Display last week result and current week result Location wise to compare the Inventory.
For this, I have created two tables 1.LastweekData and 2.CurrentweekData table.
When I am trying to display I can see the only total result of two tables but not location wise also I tried to link tables with Itemid, locationID but I see an error saying column must have a unique value. Please Help...
Note: Tables don't have Date key (My columns ITEMID,Location,Locationid,value$)
My result should be
Locationid LocationName CurrentWeekValue LastweekValue
From Table1 From Table2
1 CA 24$ 23$
2 TX 23$ 28$
Please Let me know if I can do this any other way?
Solved! Go to Solution.
Hey,
I would create a calculated column in your currentweek table like so:
LastWeekValue = LOOKUPVALUE( 'LastweekData'[Value] ,'LastweekData'[Locationid], 'CurrentweekData'[Locationid]
,'LastweekData'[ITEMID], 'CurrentweekData'[ITEMID] )
I assume that the column ITEMID is not something like a product id but uniquely identifies a row in each table, meaning it's a technical column and not a business related column. In this case you have to delete the line that contains the reference to ITEMID in the above statement.
Here is a more information about the LOOKUPVALUE function:
https://msdn.microsoft.com/de-de/library/gg492170(v=sql.120).aspx
Please be aware that you may overlook values that are not present in the CurrentweekData table, but have values in your LastweekData table.
You may consider a little remodelling of your data model, by creating a Location Table containing the columns LocationID and LocationName with the unique values of both tables and then create a relationship with the Currentweek and Lastweek table and then just use the Location information from this table in your reports.
Hopefully this is what you are looking for
Regards
Tom
Hi @PeterM,
Have you tried the solution provided by @TomMartens above? Does it work in your scenario? If it works, could you accept it as solution to close this thread?
If you still have any question on this issue, feel free to post here.
Regards
Hey,
I would create a calculated column in your currentweek table like so:
LastWeekValue = LOOKUPVALUE( 'LastweekData'[Value] ,'LastweekData'[Locationid], 'CurrentweekData'[Locationid]
,'LastweekData'[ITEMID], 'CurrentweekData'[ITEMID] )
I assume that the column ITEMID is not something like a product id but uniquely identifies a row in each table, meaning it's a technical column and not a business related column. In this case you have to delete the line that contains the reference to ITEMID in the above statement.
Here is a more information about the LOOKUPVALUE function:
https://msdn.microsoft.com/de-de/library/gg492170(v=sql.120).aspx
Please be aware that you may overlook values that are not present in the CurrentweekData table, but have values in your LastweekData table.
You may consider a little remodelling of your data model, by creating a Location Table containing the columns LocationID and LocationName with the unique values of both tables and then create a relationship with the Currentweek and Lastweek table and then just use the Location information from this table in your reports.
Hopefully this is what you are looking for
Regards
Tom
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |