Forum Discussion

alir22456's avatar
alir22456
Icon for Helper I rankHelper I
2 years ago
Solved

Power BI DAX

I have the below Data Model:


1. Service table = Service 1
2. Comparison Service table = Service 2

 

 

I have two slicers in my report for selection of services. First slicer is mapped to Service 1 and second one is mapped to Service 2.

I want to find the customers list based on slicer selection who bought Service 2 but not Service 1. I have a DAX measure that returns 1 if the customer bought service 1 but not service 2.

 

Below is the DAX measure:

 

Customers who bought service 1 but not 2 =
VAR _Service1Customers =
    CALCULATETABLE(
        DISTINCT(SalesData[Cust_ID]),
        FILTER(
            ALL(SalesData),
            SalesData[Service] IN VALUES(Service[Service])
        )
    )
VAR _Service2Customers =
    CALCULATETABLE(
        DISTINCT(SalesData[Cust_ID]),
        FILTER(
            ALL(SalesData),
            SalesData[Service] IN VALUES('Comparison Service'[Service])
        )
    )
VAR _BothServicesCustomers =
    EXCEPT(_Service1Customers, _Service2Customers)
RETURN
    IF(
        SELECTEDVALUE(SalesData[Cust_ID]) IN _BothServicesCustomers,
        1,
        0
    )
 
I am trying to do the opposite where it should return 1 if the customer bought Service 2 but not Service 1 but I'm getting wrong or empty results. Can someone please on this
 
Note: I don't want to create a relationship between the comparison Service table and SalesData table.
Need Help
  • AmiraBedh's avatar
    AmiraBedh
    2 years ago

    I think you need to just adjust your Except to revert the logic :

     

    Customers who bought service 2 but not 1 =
VAR _Service1Customers =
    CALCULATETABLE(
        DISTINCT(SalesData[Cust_ID]),
        FILTER(
            ALL(SalesData),
            SalesData[Service] IN VALUES(Service[Service])
        )
    )
VAR _Service2Customers =
    CALCULATETABLE(
        DISTINCT(SalesData[Cust_ID]),
        FILTER(
            ALL(SalesData),
            SalesData[Service] IN VALUES('Comparison Service'[Service])
        )
    )
VAR _Service2ButNotService1Customers =
    EXCEPT(_Service2Customers, _Service1Customers)
RETURN
    IF(
        SELECTEDVALUE(SalesData[Cust_ID]) IN _Service2ButNotService1Customers,
        1,
        0
    )

2 Replies

  • AmiraBedh's avatar
    AmiraBedh
    Icon for Super User rankSuper User
    2 years ago

    I think you need to just adjust your Except to revert the logic :

     

    Customers who bought service 2 but not 1 =
VAR _Service1Customers =
    CALCULATETABLE(
        DISTINCT(SalesData[Cust_ID]),
        FILTER(
            ALL(SalesData),
            SalesData[Service] IN VALUES(Service[Service])
        )
    )
VAR _Service2Customers =
    CALCULATETABLE(
        DISTINCT(SalesData[Cust_ID]),
        FILTER(
            ALL(SalesData),
            SalesData[Service] IN VALUES('Comparison Service'[Service])
        )
    )
VAR _Service2ButNotService1Customers =
    EXCEPT(_Service2Customers, _Service1Customers)
RETURN
    IF(
        SELECTEDVALUE(SalesData[Cust_ID]) IN _Service2ButNotService1Customers,
        1,
        0
    )