Forum Discussion
Check for different values in the same row
Hi,
I would like to create a measure which is checking whether a row contains different values. Actually in my company we have several affiliated entities. And it might occur that 2 (or even more) affiliated entities buy from the same external supplier, but at different prices. Those materials where our group is paying different prices I would like to identify. The purpose of my measure will be to filter only for those materials where we see different purchase prices among our entities. Like in the table below. For Material "B" you can see that among the 4 different purchase organisation in my group we don't have same pricing. I.e. my measure should identify materialID "B" as an item with different purchase prices.
Since I am more or less a beginner in "PBI" I have no clue how to make it. Is there anybody with a suggestion how to get my measure established. Maybe worth to be mentioned: MaterialID, PurOrg and the Purchase price are all from the same table in our company data set.
Your suggestions are very much appreciated.
Br
Harry
| Material ID | PurOrg 1 | PurOrg 2 | PurOrg 3 | PurOrg 4 | Check for different purchase prices |
| A | 1 € | 1 € | 1 € | 1 € | FALSE |
| B | 2 € | 3 € | 5 € | 2 € | TRUE |
| C | 2 € | 2 € | 2 € | 2 € | FALSE |
6 Replies
Hi Harry1980 ,
You can create a column like below :-
Column = VAR PurOrg1 = TRIM ( LEFT ( [PurOrg 1], SEARCH ( " €", [PurOrg 1], 1, 0 ) ) ) VAR PurOrg2 = TRIM ( LEFT ( [PurOrg 2], SEARCH ( " €", [PurOrg 2], 1, 0 ) ) ) VAR PurOrg3 = TRIM ( LEFT ( [PurOrg 3], SEARCH ( " €", [PurOrg 3], 1, 0 ) ) ) VAR PurOrg4 = TRIM ( LEFT ( [PurOrg 4], SEARCH ( " €", [PurOrg 4], 1, 0 ) ) ) RETURN IF ( PurOrg1 = PurOrg2 && PurOrg1 = PurOrg3 && PurOrg1 = PurOrg4, TRUE (), FALSE () )Output:-
Thanks,
Samarth
For Measure, you can use this
Check for different purchase prices = IF ( SELECTEDVALUE ( 'Table'[IDPurorg] ) = SELECTEDVALUE ( 'Table'[1Purorg] ) && SELECTEDVALUE ( 'Table'[IDPurorg] ) = SELECTEDVALUE ( 'Table'[2Purorg] ) && SELECTEDVALUE ( 'Table'[IDPurorg] ) = SELECTEDVALUE ( 'Table'[3Purorg] ) && SELECTEDVALUE ( 'Table'[1Purorg] ) = SELECTEDVALUE ( 'Table'[2Purorg] ) && SELECTEDVALUE ( 'Table'[1Purorg] ) = SELECTEDVALUE ( 'Table'[3Purorg] ) && SELECTEDVALUE ( 'Table'[2Purorg] ) = SELECTEDVALUE ( 'Table'[3Purorg] ), "FALSE", "TRUE" )OutPut : -
Hi Vairag99, thank you for your support. I think there is a misunderstanding due to showing my table as a pivot. I apologize. For my measure I will need to use 3 fields from the same table ("PurchasePrice").
1) dimPurchasePrice[MaterialID]
2)dimPurchasePrice[PurOrg]
3)dimPurchasePrice[Price]
All the needed purchase org info are retrieved from only 1 field, namely dimPurchasePrice[PurOrg]. I.e. the field dimPurchasePrice[PurOrg] can have the following values. PurOrg 1, PurOrg2, PurOrg3 or PurOrg4 (like below)
Do you also have an idea to get it work with the layout below?
Many thanks in advance
Br
Harry
Material ID PurOrg Price A PurOrg1 2 A PurOrg2 2 A PurOrg3 2 A PurOrg4 2 B PurOrg1 2 B PurOrg2 3 B PurOrg3 5 B PurOrg4 2 I hope this layout is better and understandable
