topic How to show a list of all parts that are related to some selected products and not to others? in DAX Commands and Tips

How to show a list of all parts that are related to some selected products and not to others?

Anonymous — Thu, 18 Apr 2024 09:30:48 GMT

Hello to you all and thanks for all the help you could bring on this 🙂

I've tried to find any other thread, but I'm not sure there is one for this doubt.

Sorry if I duplicate and/or misplaced this question.

I have a table where I relate Products and Parts, each product uses varios parts and each part, could be (or not) used in many products.

Products	Parts
Prod-A	Part-1
Prod-A	Part-3
Prod-A	Part-4
Prod-B	Part-2
Prod-B	Part-3
Prod-C	Part-1
Prod-C	Part-4

The goal we have is to have a dynamic list that only shows the parts that are ONLY related to the products selected on a visual.

As if for example, we have product A and C selected, we show only Parts 1 and 4 (because Part-2 is not in Prod-A nor Prod-C and Part-3 is in Prod-A but also in Prod-B, and Prod-B is not selected in the visual)

If anyone could help us with it it would be really apreciated.

Thanks in advance!

UPDATE_____________________________

PBIX file here:.

https://drive.google.com/file/d/1myxBVZUa0MzUzitUFyEiaAFWOMIHIEl_/view?usp=drive_link

I think that might help to understand a bit better what I was trying to say. Sorry if I wasn't clear enough ^^

I'm trying to create a DAX similar to this:

But I don't know why is not working (Also I had to put a CONCATENATEX because otherwise when I added this measure to a table it kept saying that was containing more values than expected (it expected 1) but the whole point of the measure is to create those many values.

I'm not sure what I'm doing wrong here.

It would be a great help if somebody could give me a hint or something

Thanks!

Re: How to show a list of all parts that are related to some selected products and not to others?

Jihwan_Kim — Sun, 07 Apr 2024 04:39:42 GMT

Hi,

I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

Parts list expected result: = VAR _condition = CALCULATE ( COUNTROWS ( 'Product' ), ALLSELECTED ( 'Product' ) ) RETURN INT ( _condition = CALCULATE ( COUNTROWS ( Data ), ALLSELECTED ( 'Product'[Products] ) ) )

Re: How to show a list of all parts that are related to some selected products and not to others?

AnalyticsWizard — Sun, 07 Apr 2024 08:52:45 GMT

To create a dynamic list in Power BI that only shows the parts related to the selected products, you can use the FILTER function in DAX 1. Here’s an example of how you might write this measure:

DynamicPartsList = 
CALCULATE(
    VALUES(Table1[Parts]),
    FILTER(
        ALL(Table1),
        COUNTROWS(
            FILTER(
                VALUES(Table1[Products]),
                CALCULATE(
                    COUNTROWS(Table1),
                    ALLEXCEPT(Table1, Table1[Products])
                ) > 0
            )
        ) > 0
    )
)

In this measure, Table1 is the name of your table. This measure creates a list of parts that are related to the selected products. The ALL function is used to remove any filters that might limit the rows being evaluated. The VALUES function is used to get a list of the selected products, and the FILTER function is used to filter the parts based on these selected products 1.

Please replace Table1 with your actual table name. If you need further assistance or have more specific requirements, feel free to ask! 😊

Re: How to show a list of all parts that are related to some selected products and not to others?

ThxAlot — Sun, 07 Apr 2024 15:24:49 GMT

Simple enough,

Re: How to show a list of all parts that are related to some selected products and not to others?

Anonymous — Thu, 18 Apr 2024 09:28:26 GMT

Hi Jihwan_Kim Jihwan_Kim!

Thanks for the help ^^ Sadly, it has not quite the same behavior as I've tried to describe, my bad.

I'll try to clarify on this.

At least the PBIX file you attached uses the slicer as normal. If I select Prod B for example, shows part 2 and 3. But because 3 is also in Prod A, we shouldn't be displaying it in the list, the list when just Prod B is selected in the slicer should be just Part 2, since it's the only part related to Prod B and not to anyone else.

Same thing happens when selecting Prod C (shows Part 1 and 4, instead of just part 4) or when selecting multiple Prods like Prod B and C

Could be that?

Thanks again 🙂

Re: How to show a list of all parts that are related to some selected products and not to others?

Anonymous — Thu, 18 Apr 2024 09:28:52 GMT

Hi AnalyticsWizard , thanks to you as well.

But I do not know why is not working for me this measures. they just list the parts directly related to the products selected, nothing more nothing less.

Re: How to show a list of all parts that are related to some selected products and not to others?

Anonymous — Thu, 18 Apr 2024 09:29:27 GMT

Hi ThxAlot !

Thanks for the help, but it's more or less like Jihwan_Kim solution.

At least the PBIX file you attached uses the slicer as normal. If I select Prod B for example, shows part 2 and 3. But because 3 is also in Prod A, we shouldn't be displaying it in the list, the list when just Prod B is selected in the slicer should be just Part 2, since it's the only part related to Prod B and not to anyone else.

Same thing happens when selecting Prod C (shows Part 1 and 4, instead of just part 4) or when selecting multiple Prods like Prod B and C

I'll add to the description a better explanation

Thanks again