CODEWITHSUNDEEP
powerbidaxslicers
KyleF
KyleF

Reputation: 135

Return values matching 2 selections but not matching a 3rd?

New to DAX and PowerBI and am trying to solve a particular problem.

I have a dataset that is approximated by the following (with column names Customer, Department, Merchant, and Product, respectively):

Super Mario World = {
("Yoshi's Island", "Performance Enhancers", "Toad", "Mushroom"),
("Yoshi's Island", "Performance Enhancers", "Toad", "Fire Flower"),
("Vanilla Dome", "Performance Enhancers", "Toad", "Fire Flower"),
("Vanilla Dome", "Performance Enhancers", "Toad", "Star"),
("Chocolate Island", "Performance Enhancers", "Wendy O. Koopa", "Dino Torch"),
("Chocolate Island", "Performance Enhancers", "Dino Rhino", "Dino Torch"),
("Chocolate Island", "Obstacle", "Dino Rhino", "Hot Mud"),
("Valley of Bowser", "Obstacle", "Toad", "Koopa Shell"),
("Valley of Bowser", "Obstacle", "Koopa Troopa", "Koopa Shell"),
("Valley of Bowser", "Performance Enhancers", "Toad", "Mushroom"),
("Donut Plains", "Obstacle", "Koopa Troopa", "Hammers"),
("Donut Plains", "Obstacle", "Koopa Troopa", "Bullet Bill"),
("Donut Plains", "Performance Enhancers", "Toad", "Cape Feather"),
("Donut Plains", "Performance Enhancers", "Toad", "Fire Flower")
}

The major difference between the above table and my actual data is that Customer, Department, Merchant, and Product are all in individual tables that have relationships to a central fact table. I couldn't figure out how to make that clear in this post other than to state it, so apologies.

I would like to return all customers that meet the selection for department and merchant but do NOT meet the selection for product. So, if I select Performance Enhancers, Toad, and Mushroom, I want to return Vanilla Dome and Donut Plains. I do not want to return Valley of Bowser or Yoshi's Island because Mushroom is a product associated with those customers. Similarly, if I selected Obstacle, Koopa Troopa, and Koopa Shell, I'd want to return Donut Plains only.

I've tried a ton of iterations of this, most prominently something like this https://community.powerbi.com/t5/Desktop/Tricky-Slicer-Options/m-p/573381#M270846. The goal in that example is to create a disconnected table and use that disconnected table as a slicer in conjunction with that calculation. But I haven't been successful at making it work.

I also made a couple of efforts at writing a measure that defined different variables for each of the the three conditions and then a simple if statement. Something like this:

Customer Filter = 
VAR a = (
    SuperMarioWorld[Department]
    )
VAR b = (
    SuperMarioWorld[Merchant]
    )
VAR c = (
    SuperMarioWorld[Product]
    )
RETURN
IF (
    SELECTEDVALUE(SuperMarioWorld[Department]) = a &&
    SELECTEDVALUE(SuperMarioWorld[Merchant]) = b &&
    NOT SELECTEDVALUE(SuperMarioWorld[Department]) = c,
    TRUE,
    FALSE
)

This hasn't worked for me (and doesn't even register as valid for a number of reasons I can't totally decipher).

Any help would be greatly appreciated!

Upvotes: 0

Views: 65

Answers (1)

Jos Woolley
Jos Woolley

Reputation: 9062

It simplifies things that, from your description, it's clear that you will be disallowing multiple selections per slicer.

Define a measure:

MyFilter :=
VAR SelectedProduct =
    MIN( Products[Product] )
VAR SelectedMerchant =
    MIN( Merchants[Merchant] )
VAR SelectedDepartment =
    MIN( Departments[Department] )
VAR MyFilter1 =
    CALCULATE(
        COUNTROWS(
            FILTER(
                Data,
                Data[Department] = SelectedDepartment
                    && Data[Merchant] = SelectedMerchant
            )
        ),
        ALL( Products[Product] )
    ) > 0
VAR MyFilter2 =
    COUNTROWS(
        FILTER(
            Data,
            Data[Product] = SelectedProduct
        )
    ) = 0
RETURN
    MyFilter1 * MyFilter2

You can then drag this measure into the filters area for the visual and set it equal to 1.

Upvotes: 1

Related Questions