Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Help: Distinc Count in some columns- DAX help

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

 

 

 

Need Help

5 Replies

  • parry2k's avatar
    parry2k
    Super User
    8 years ago

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

  • v-huizhn-msft's avatar
    v-huizhn-msft
    Microsoft Employee
    8 years ago

    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's avatar
      Anonymous
      Not applicable
      8 years ago

      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