cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
MonicaElizondo Regular Visitor
Regular Visitor

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

 

 

10.JPG

 

1 ACCEPTED SOLUTION

Accepted Solutions
v-huizhn-msft Super Contributor
Super Contributor

Re: Help: Distinc Count in some columns- DAX help

Hi @MonicaElizondo,

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

Best Regards,
Angelia

5 REPLIES 5
Super User
Super User

Re: Help: Distinc Count in some columns- DAX help

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




Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut! Connect with me on Linkedin






MonicaElizondo Regular Visitor
Regular Visitor

Re: Help: Distinc Count in some columns- DAX help

@parry2k

 

v-huizhn-msft Super Contributor
Super Contributor

Re: Help: Distinc Count in some columns- DAX help

Hi @MonicaElizondo,

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
)

1.GIF

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]
)

2.GIFresult

Best Regards,
Angelia

MonicaElizondo Regular Visitor
Regular Visitor

Re: Help: Distinc Count in some columns- DAX help

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 Super Contributor
Super Contributor

Re: Help: Distinc Count in some columns- DAX help

Hi @MonicaElizondo,

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

Best Regards,
Angelia