Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

Hi @Anonymous,

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

Best Regards,
Angelia

View solution in original post

5 REPLIES 5
v-huizhn-msft
Employee
Employee

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
)

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

resultresult

Best Regards,
Angelia

Anonymous
Not applicable

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

Hi @Anonymous,

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

Best Regards,
Angelia

parry2k
Super User
Super User

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

@parry2k

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.