Help: Distinc Count in some columns- DAX help

[Anonymous]

Hello!!

Im new on PBI and DAX, I want to know if there is a way to do this in dax, I have a table with many fields including the supplier, the material, the plant, the country and the unit price of products, I want to obtain the suppliers that sell the same material in different plants at a price different, in the same country.

I appreciate your help!

Mónica

[v-huizhn-msft]

Hi @Anonymous,

I test using your sample data and get expected result as follows.

1. Add a index column in Power query editor, please review more details from here.

2 Create a calculated columns based on index.

Price_Or =
RANKX (
    FILTER (
        'Sample',
        'Sample'[Country] = EARLIER ( 'Sample'[Country] )
            && 'Sample'[Material] = EARLIER ( 'Sample'[Material] )
            && 'Sample'[Supplier] = EARLIER ( 'Sample'[Supplier] )
    ),
    'Sample'[Price],
    ,
    ASC
)

Plant_Or =
RANKX (
    FILTER (
        'Sample',
        'Sample'[Country] = EARLIER ( 'Sample'[Country] )
            && 'Sample'[Material] = EARLIER ( 'Sample'[Material] )
            && 'Sample'[Supplier] = EARLIER ( 'Sample'[Supplier] )
    ),
    'Sample'[Plant],
    ,
    ASC
)

3. Create another two columns based on the columns above.

Price_tag =
IF (
    LOOKUPVALUE ( 'Sample'[Price_Or], 'Sample'[Index], 'Sample'[Index] + 1 )
        <> 'Sample'[Plant_Or]
        || IF (
            'Sample'[Index] = 1,
            'Sample'[Price_Or],
            LOOKUPVALUE ( 'Sample'[Price_Or], 'Sample'[Index], 'Sample'[Index] - 1 )
        )
            <> 'Sample'[Plant_Or],
    1,
    0
)


Plant_tag =
IF (
    LOOKUPVALUE (
        'Sample'[Plant_Or],
        'Sample'[Index], 'Sample'[Index] + 1,
        'Sample'[Country], 'Sample'[Country]
    )
        <> 'Sample'[Plant_Or]
        || IF (
            'Sample'[Index] = 1,
            'Sample'[Plant_Or],
            LOOKUPVALUE (
                'Sample'[Plant_Or],
                'Sample'[Index], 'Sample'[Index] - 1,
                'Sample'[Country], 'Sample'[Country]
            )
        )
            <> 'Sample'[Plant_Or],
    1,
    0
)

4. Then create a new table by clicking New table under Modeling on Home page, type the formula below and get right result.

Table =
SELECTCOLUMNS (
    FILTER ( 'Sample', 'Sample'[Price_tag] = 1 && 'Sample'[Plant_tag] = 1 ),
    "Country", 'Sample'[Country],
    "Po Number", 'Sample'[Po Number],
    "Po item", 'Sample'[Po item],
    "Supplier", 'Sample'[Supplier],
    "Material", 'Sample'[Material],
    "Plant", 'Sample'[Plant],
    "Price", 'Sample'[Price]
)

result

Best Regards,
Angelia

[Anonymous]

Thanks for your help and your time, 

I tried this solutions and i dont get the result that I hope.

Im get the same material, the same supplier, the same plant ; am going to tried chance a little this solution.

Thanks and greetings,

Monica

[v-huizhn-msft · Accepted solution]

Hi @Anonymous,

Please download the .pbix file(attachment) for more details.

Best Regards,
Angelia

[parry2k]

@Anonymouscan you provide sample daata set in excel sheet to get you the solution. Thanks

[Anonymous]

@parry2k

Thanks, im uploading the excel with a sample set; is asample, the original has a lot of columns.

https://drive.google.com/file/d/1NN6Mh85EnYPg8wByJ5REkZAdNNWWHT9r/view?usp=sharing