Get a corresponding price from another table using relationship through item table
Hi! I have this pretty complex problem (at least for me) that I can't seem to solve by myself. So basically I have this table called "Latest purchase price", which has information about every items latest purchased. I also have an item table and then I have a trade agreements table that contains information about every item's trade agreements for different purchase quantities, thus the relationship between that table and item table is many-to-one. I'll post a picture here about the data model if that helps. I want to compare latest purchase price to the price that we have in trade agreements thus I need to get the corresponding value from the trade agreements table for the latest purchase. I'll post a mockup data here also so that you can try it yourself. I have tried to do this using summarize columns and sumx. The logic is that once I have the virtual table with trade agreements for given item, I can use filter condition to filter it only contain values where purchased qty is equal or greater than fromqty in trade agreements table. Still it is ambiguous so using minx I want to just get one value which should be correct. But as you can see from picture bellow my dax query is not working... Trade agreement price = var latest_qty = MAXX('Latest Purch Prices', 'Latest Purch Prices'[OrderedQty]) var trade_agreement_qty = FILTER(SUMMARIZECOLUMNS( vDimItem[ItemID], TradeAgreements[AMOUNT], TradeAgreements[QUANTITYAMOUNTFROM]), TradeAgreements[QUANTITYAMOUNTFROM] <= latest_qty) return MINX(trade_agreement_qty, TradeAgreements[QUANTITYAMOUNTFROM]) Latest Purch Price Table: ID ITEM PRICE ORDEREDQTY 1 1234 1 1 2 1235 2 10 3 1236 11 3 vDimItem: ID ITEMID 1 1234 2 1235 3 1236 TradeAgreements: ITEMRELATION FROMQTY AMOUNT 1234 0 1 1235 0 5 1235 10 2 1236 0 10 1236 5 5 1236 10 2 Goal/Result: ID ITEM PRICE ORDEREDQTY TRADEAGREEMENT PRICE Difference 1 1234 1 1 1 0 2 1235 2 10 2 0 3 1236 11 3 2 -1
Unwanted Aggregation with dynamic Ranking
Hi, I am trying to create a dynamic ranking measure. Lets say I have n rows after every filter operation. Now I want the rows enumerated from 1.. n, according to their original id (which is unique). link to pbix <- here is a reproduction of the error. Password: Example%5 I tried the solution by @Eirc_Zhang from this post but it does not work for me. Here is the code how I modiefied it, to use the index as the parameter for ranking. RankID = MINX ( FILTER ( SELECTCOLUMNS ( ALLSELECTED ( ParentData ); "index"; ParentData[Index]; "rank"; RANKX ( ALLSELECTED ( ParentData ); ParentData[Index];; ASC; DENSE ) ); [index] = MAX ( ParentData[Index] ) ); [rank] ) Everytime I use a visual with non unique field combinations, the rows are aggregated, as you can see in Visual (1) in my posted image. In Visual (2) the unique field "Index" is added and then there is no aggregation. The final goal would be to use a non unique field and the RankID togehter in a scatter plot (Visual 3). Since you can't add there another field there would be always an unwanted aggregation. Can someone help me to fix the problem? (I already managed to do it with a python visual, but I would rather have an interactive visual. I kinda wish, I could use python to calculate measures ) I actually don't fully understand the linked solution and would appreciate, if someone could help me to understand it a little better. For example I have a hard time to understand how the MINX function works, since the inner filter call just returns a table with a single row as output, as you can see in the next image. Best regards, AndreasDAX to show Total value by Month highlighting High and Low or Max and Min data points.
Hello Community Members I'm struggling with this DAX, objective is to create a measure to show High and Low data points for total utility consumption per month. The date is coming from the date table which got Date, Year, Month of Year, Quarter of Year, Day of Month as field list. [Total Utility] is DAX measure. With this DAX, I'm aiming to achieve 1 against High and Low value by month and then with the help of conditional formatting, I highlight '1' in my Line chart or Clustered Column. But somehow my DAX is not working, Ideally i like to see 1 against the blue circled value., instead I'm only getting 1 for the highest value for the whole selected range for the year. Tried using MINX / MAXX with ALLSELECTED or SUMMARIZE and ADDCOLUMNS nothing working. My current DAX which is not giving the desired output is below mentioned. Please suggest any tweaks or new DAX. OVERALLMAXMIN = VAR ELECTRIC = [Total Electric] VAR MAXELECTRIC = MAXX(ALLSELECTED('Date'[Year],'Date'[Month]),[Total Electric]) VAR MINELECTRIC = MINX(ALLSELECTED('Date'[Year],'Date'[Month]),[Total Electric]) VAR RESULTS = IF(ELECTRIC=MAXELECTRIC || ELECTRIC=MINELECTRIC, 1, 0 ) RETURN RESULTS
