Power BI Visual

[alir22456]

Hi,

I am working on a Power BI cross selling project. My data is arranged in this format:

I have created a report that consists of two table viusals and a slicer. The slicer consists of the services as below:

We can select the services from the slicer and the table visual will display the list of customers who bought that specific service (or services) as shown below:

Example:

Plan10 customers = Customer1, Customer2, Customer4, Customer5, Customer6, Customer8, Customer11 (This is the result of Table1 visual)

Plan10 and Plan4 customers = Customer1, Customer5, Customer6 (This is the result of Table2 visual)

**Targeted customers (who bought Plan10 but not Plan4) = Customer2, Customer4, Customer8, Customer11

(I need to create this Table3 visual by subtracting Table2 visual values from Table1 visual values)

Currently I am doing this process manually by exporting the table values as csv and then comparing it in Excel. Is it possible to achieve this in Power BI?

Thanks.

[Anonymous · Accepted solution]

Hi @alir22456 ,

@ryan_mayu Thanks for your reply!

And @alir22456 you can try this way:
Here is my sample data:

Use this DAX to create two calculated tables for slicers (If only one slicer won't do what you need it to do):

Buy = VALUES('Table'[Services])

Not Buy = VALUES('Table'[Services])

Please note that there is no relationship between tables:

Use this DAX to create a measure to return Customers who have only bought the Services selected in the first slicer:

Buy_single_Customers = 
CONCATENATEX(
    FILTER(
        'Table',
        'Table'[Services] IN VALUES(Buy[Services])
    ),
    'Table'[Customers],
    ","
)

Continue to use this DAX to create a measure that returns Customers who bought both the Services selected in the first slicer and the Services selected in the second slicer:

Buy_both_Customers = 
VAR _BUY = 
CALCULATETABLE(
    DISTINCT('Table'[Customers]),
    FILTER(
        ALL('Table'),
        'Table'[Services] IN VALUES(Buy[Services])
    )
)
VAR _NOT_BUY = 
CALCULATETABLE(
    DISTINCT('Table'[Customers]),
    FILTER(
        ALL('Table'),
        'Table'[Services] IN VALUES('Not Buy'[Services])
    )
)
VAR _Both = 
INTERSECT(_BUY, _NOT_BUY)
RETURN
CONCATENATEX(
    _Both,
    'Table'[Customers],
    ","
)

Finally this DAX is used to create a measure to return Customers who only bought the Services selected in the first slicer and not the Services selected in the second slicer:

Both - Single = 
VAR _BUY = 
CALCULATETABLE(
    DISTINCT('Table'[Customers]),
    FILTER(
        ALL('Table'),
        'Table'[Services] IN VALUES(Buy[Services])
    )
)
VAR _NOT_BUY = 
CALCULATETABLE(
    DISTINCT('Table'[Customers]),
    FILTER(
        ALL('Table'),
        'Table'[Services] IN VALUES('Not Buy'[Services])
    )
)
VAR _Both = 
EXCEPT(_BUY, _NOT_BUY)
RETURN
CONCATENATEX(
    _Both,
    'Table'[Customers],
    ","
)

And the final output is as below:

Best Regards,
Dino Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[alir22456]

Thanks @Anonymous 

[ryan_mayu]

one way is that you can create 8 pages and use button to navigate to different page.

and in each page's page filter, you need to select the customer that in that plan scope

the other way is to update the DAX to get that result. 

I think the first way is easier to maintenance your report in the future.

[alir22456]

Hi @ryan_mayu 

Thanks but the data I showed is just for understanding. I have a huge dataset so creating pages seperately will not be a practical solution. And the filter is applied through a slicer. I cannot figure out a way to create a DAX measure that filters the results without using a slicer that dynamically updates the result based on the selected option. Is there a way to subtract the list values of the table visuals (not the actual table) and get the results.

Thanks 

[ryan_mayu]

@alir22456

 or you can create a mapping table and filter this table to get the corresponding output.

| Plan   | Customer   |
| ------ | ---------- |
| Plan 1 | Customer 1 |
| Plan 1 | Customer 2 |
| Plan 1 | Customer 3 |
| Plan 1 | Customer 4 |
| Plan 2 | Customer 3 |
| Plan 2 | Customer 4 |
| Plan 2 | Customer 5 |