Check for different values in the same row

[Harry1980]

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

[Anonymous]

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 : - 

[Harry1980]

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

[Harry1980]

I hope this layout is better and understandable

[Anonymous · Accepted solution]

The Answer is Here. 

Measure =
VAR _count =
CALCULATE (
DISTINCTCOUNT ( 'Table (7)'[Price] ),
ALLEXCEPT ( 'Table (7)', 'Table (7)'[Material ID] )
)
RETURN
IF ( _count = 1, "FALSE", "TRUE" )

[Harry1980]

@Anonymous , it is working as expected. Thank you very much. Your support is much appreciated

[Samarth_18]

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