Forum Discussion

alir22456's avatar
alir22456
Helper I
2 years ago
Solved

Power BI Visual

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...
Need Help
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

ryan_mayu's avatar
ryan_mayu
Super User
2 years ago

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's avatar
alir22456
Helper I
2 years ago

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's avatar
    ryan_mayu
    Super User
    2 years ago

    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 |