Use slicer to filter values in at least 1 of 2 fields

[Anonymous]

Firstly, thank you for wanting to help!

I currently have data that looks something like this:

First Side Code	Second Site Code	Audio	Video	Audio Poor	Video Poor
ABC	DEF	1	0	1	0
DEF	XYZ	2	2	1	1
XYZ	FGH	1	1	0	0

I would like to get the data to something like this:

Site Code	Audio	Video	Audio Poor	Video Poor
ABC	1	0	1	0
DEF	3	2	2	1
XYZ	3	3	1	1
FGH	1	1	0	0

This would ultimately allow me to create a slicer returning all of the data for my chosen site regardless of whether it was the first site, second site or both (if both data should only be counted once).

I am pretty new to Power BI so detailed answers would be appreciated. Thanks!

[Phil_Seamark]

Hi @Anonymous

This calculated table gets close, but seems to be overcounting.  Do you have a special rule to be applied when the item is a secondary code?  I have attached a PBIX file.

Table = 
SUMMARIZE(
    UNION
        (
        SELECTCOLUMNS('Table1',"Site Code",[First Side Code],"Audio",[Audio] , "Video",[Video] , "Audio Poor",[Audio Poor] , "Video Poor",[Video Poor]),
        SELECTCOLUMNS('Table1',"Site Code",[Second Site Code],"Audio",[Audio] , "Video",[Video] , "Audio Poor",[Audio Poor] , "Video Poor",[Video Poor])
        ),
        [Site Code],
        "Audio" , SUM(Table1[Audio]),
        "Video" , SUM('Table1'[Video]) ,
        "Audio Poor" , SUM('Table1'[Audio Poor]) ,
        "Video Poor" , SUM('Table1'[Video Poor])
        
        
        )

[Anonymous]

Thanks @Phil_Seamark for giving it a go!

To answer your question - it's simply no.

Does looking at it as 2 tables help at all?

First Side Code	Audio	Video	Audio Poor	Video Poor
ABC	1	0	1	0
DEF	2	2	1	1
XYZ	1	1	0	0

Second Site Code	Audio	Video	Audio Poor	Video Poor
DEF	1	0	1	0
XYZ	2	2	1	1
FGH	1	1	0	0

[Ashish_Mathur]

Hi,

If data is in two Tables, then they can simply be appended.  The appended Table can then be grouped on the first column.

[Phil_Seamark · Accepted solution]

Hi @Anonymous

I just needed to use a different summarisation function.  Please try this calculated table instead

New Table = 
VAR T1 =  UNION
        (
        SELECTCOLUMNS('Table1',"Site Code",[First Side Code],"A",[Audio] , "V",[Video] , "A Poor",[Audio Poor] , "V Poor",[Video Poor]),
        SELECTCOLUMNS('Table1',"Site Code",[Second Site Code],"A",[Audio] , "V",[Video] , "A Poor",[Audio Poor] , "V Poor",[Video Poor])
        )
RETURN
   GROUPBY(
       T1,
       [Site Code],
       "Audio",SUMX(CURRENTGROUP(),[A]),
       "Video",SUMX(CURRENTGROUP(),[V]),
       "Audio Poor",SUMX(CURRENTGROUP(),[A Poor]),
       "Video Poor",SUMX(CURRENTGROUP(),[V Poor])
       )

[Anonymous]

@Phil_Seamark- can you send me the update Power BI file?

[Phil_Seamark]

Sure,

Sorry, I should have added that to my earlier reply.

[Anonymous]

Great - thank you very much @Phil_Seamark!

If you don't mind, I have one more question - I actually have a lot of additional columns and measures in my original table is there a quicker way for me to pull them through?

[Phil_Seamark]

You can try the same approach in the Power Query Editor.  

Basically, make two copies of your source table.  Remove the [First Site Code] column from one and then the [Second Site Code] column from the other.  Once you have done that you can append the two queries on top of each other.  This won't aggregate the rows that appear in both - so you will still need to write some "grouping" code in PQ or DAX, whichever you prefer.

[Anonymous]

I think the grouping is causing rows that have the same site for first and second, to be counted twice. In the original question, I did mention that I was only looking for these to be counted once.

Try adding another line of data to the table and you will see:

[Phil_Seamark]

HI @Anonymous

I think we just need to add a FILTER to the second table in the UNION function as follows

Table = 
VAR T1 =  UNION
        (
        SELECTCOLUMNS('Table1',"Site Code",[First Side Code],"A",[Audio] , "V",[Video] , "A Poor",[Audio Poor] , "V Poor",[Video Poor]),
        SELECTCOLUMNS(FILTER('Table1','Table1'[First Side Code]<>'Table1'[Second Site Code]),"Site Code",[Second Site Code],"A",[Audio] , "V",[Video] , "A Poor",[Audio Poor] , "V Poor",[Video Poor])
        )
RETURN
   GROUPBY(
       T1,
       [Site Code],
       "Audio",SUMX(CURRENTGROUP(),[A]),
       "Video",SUMX(CURRENTGROUP(),[V]),
       "Audio Poor",SUMX(CURRENTGROUP(),[A Poor]),
       "Video Poor",SUMX(CURRENTGROUP(),[V Poor])
       )