Create a single filter to filter across two columns

[Anonymous]

Hi,

I'm terribly sorry if this has been asked multiple times however I can't seem to find a quick solution to this query.

I have a dataset that has multiple columns, two of these columns are number columns with numbers between 1-12 for months (whole number). My data might look something like this:

Col1   Col2   Col3  Col4

fywh    12      3        £100

hujq     10     5         £501

I need to create a line graph with col2 & col3 as the bottom axis, col1 as the y axis and col4 as the value. Easy enough however I need to be able to toggle between Col2 & Col3 to reflect the cost at the different months.

How I've done this is to add a custom column to merge Col2 & Col3 with an identifying in between. Such as: 12, Col2; 3, Col3

After much Power Query magic I basically managed to duplicate all lines of my table so it now looks like this:

(I followed steps as per this page to action this: https://community.powerbi.com/t5/Desktop/How-do-I-duplicate-rows-based-on-cell-contents-cell-contain...

Col1   Col2    Col4    Col5

fywh    12      £100    Col2

fywh    3        £100    Col3

hujq     10     £501     Col2

hujq     5       £501     Col3

This is great because it allows me to create the line graph that I want and set a filter for Col5 to filter the dataset...in theory and small practice yes...

The problem I have is that in reality my dataset is over 2 million lines (and counting) so to action this each refresh the report just can't copy. 

There must be a simpler way of actioning this. I was thinking of a filter table with the identifyers in then a DAX measure to filter across Col2 and Col3 that way. Would this be the best thinking? I have tried a relationship but I can't see how this would work as there isn't any way of connecting to the main data (I would have to create the identifyer column to do this)

I'm sorry if this doesn't make sense! 

E

[lbendlin]

Use Calculation Groups.

[Anonymous]

Hi, 

Do you know where I can read more into this please?

Thanks 

E

[v-shex-msft]

HI @Anonymous,

You can take a look at the following methods if they suitable for your scenario:

1. Unpivot columns.
Enable 'unpivot columns' on your value fields to convert them to attribute and value, then you can simply filter these records.

Unpivot columns (Power Query) - Excel (microsoft.com)

2. Calculate group and measure filter.

Introducing Calculation Groups 

Applying a measure filter in Power BI - SQLBI

Regards,
Xiaoxin Sheng

[Anonymous]

@v-shex-msft Hi, thank you. I've taken a read to both of your solutions. 

The 'Unpivot Columns' idea did work to begin with which was fantastic but when I tried to load in all the data that was needed into the tool its kept timing out during refresh. 

I decided to re-think my options to allow for the large dataset and I've come up with the following solution in Excel that works:

Col1  Col2  Col3  Col4   NewCol5

fywh  12      3       £100 (=IF(Table2 Filter="Col2", B2,IF((H2 Filter="Col3",C2, ""))

hujq   10     5       £501  (=IF(Table2 Filter="Col2", B3,IF((H2 Filter="Col3",C3, ""))

I then plan on creating a line graph with Col5 as the Axis, Col4 as the value and a year column in the legend. 

I know that Power BI gets funny with IF statements so I'm unsure how to get around this? I've created a new Filter Table with simply "Col2","Col3" listed (with an index) and I've created an index column in my main table. I've created a relationship between the two tables through the index column.

Do you know where I'd go from here please?

Thanks! 

E

[v-shex-msft · Accepted solution]

HI @Anonymous,

#1, Unpivot columns show be a better choice to handle multiple value field interactions, but it will expand your table records so it should not suitable to work with tables that include larger amount of records.

For this scenario, you can consider extracting the category field and value fields to a new table that only includes the key fields, then do unpivot on it. (it should better than direct expand raw tables records)

#2. According to your description, it seems like you are trying to create a dynamic field that based on filter selection. For this requirement, you need to create a parameter table that stored the value field names, then you can use it on the switch function to show different table field values based on current selections.

Measure =
// parameter table stored A,B,C,D types of field name
VAR selected =
    SELECTEDVALUE ( ParaTable[Field Name] )
RETURN
    SWITCH (
        selected,
        "A", SUM ( Table[Field A] ),
        "B", SUM ( Table[Field B] ),
        "C", SUM ( Table[Field C] ),
        "D", SUM ( Table[Field D] ),
        //default and exception case
        SUM ( Table[Field A] )
    )

BTW, current power bi does not support creating a dynamic calculated column/table based on filter selections. Please use the measure formula instead.
Regards,

Xiaoxin Sheng

[Anonymous]

Hello,

Thank you for getting back to me on this, I'm sorry I didn't sooner however I took a period of leave so didn't think about this! I will certainly give this a try, thank you. The only other option I found that worked was bookmarks.

Thanks, 

E