Choosing multiple filters (A+B), but needed data is not a simple combination of A and B

[RogerMarkRowan]

I have a mathematical model in which variable A gives one set of results, B gives another, but A+B together give a set of results that is completely different.

I want to offer the user the chance to select A or B as simple selections on a graph (that's easy) but when they multi-select A and B, they get the A+B results.

I'm stumped, although it's probably very easy. Could someone please help? Thanks

[Anonymous · Accepted solution]

Hi @RogerMarkRowan ,

Do you currently have three separate data tables, where selecting 'A' displays data from Table A, selecting 'B' displays data from Table B, and when both 'A' and 'B' are selected, it shows the combined data from both tables? If that’s the case, I would recommend creating a new table by merging the three tables into a single one to simplify the process.I've made a test for your reference:

1\My data source

DatasetA

DatasetB

DatasetA+B

2\Create a new table 'Options'

3\Create a new caculate table 

Table = UNION(ADDCOLUMNS(DatasetA,"Type","DatasetA"),ADDCOLUMNS(DatasetB,"Type","DatasetB"),ADDCOLUMNS('DatasetA+B',"Type","DatasetA+B"))

4\Add a measure

DisplayThisRecord = 
If( 
    CONTAINS(Options,Options[Option],"A") && CONTAINS(Options,Options[Option],"B") && SELECTEDVALUE('Table'[Type])="DatasetA+B",
    1,
     If(CONTAINS(Options,Options[Option],"A") && Not(CONTAINS(Options,Options[Option],"B")) && SELECTEDVALUE('Table'[Type])="DatasetA",
     1,
       If(CONTAINS(Options,Options[Option],"B")&& Not(CONTAINS(Options,Options[Option],"A"))  && SELECTEDVALUE('Table'[Type])="DatasetB",
       1,
       0)
     )
)

5\Display data in Table and filter on the measure DisplayThisRecord 

Best Regards,

Bof

[RogerMarkRowan]

This feels so close...

but rather than three tables, I have one table with A, B and A+B as three columns.

I'm not sure, therefore, how to do the calculate table. I'm very new to Power BI (sorry!)

[RogerMarkRowan]

Hmmm, ok, I'll see if I can.

Dataset A contains 1, 2, 4, 8, 16, 32

Dataset B contains 1, 1, 2, 2.5, 3.2, 5.43

I can graph those simply enough (both have Year as a common factor)

Dataset (A+B) has values 6, 8.32 ,9.45 ,2.3, 43 ,1.1

Dataset (A+B) also has Year as the common factor

I'd like a graph whereby the user has a slicer visible with multi-select on. Choosing A graphs dataset A, B gives you B but select both A and B gives you the (A+B) dataset

[Selva-Salimi]

Hi @RogerMarkRowan 

you can write a measure like following to show on your visual:

measure 1 := if (selectedvalue (ID) ='A', what you want to show as A selected , if (selectedvalue (ID)='B' , what you want to show as B selected , what you want to show as A+B ))

if it does not work please share more details. and also clarify if you just have two selction A & B or more.

If this post helps, then I would appreciate a thumbs up  and mark it as the solution to help the other members find it more quickly.

[HotChilli]

Please provide more detail.

https://community.fabric.microsoft.com/t5/DAX-Commands-and-Tips/How-to-Get-Your-Question-Answered-Qu...