How can I filter my Pivoted table by it own Unpivoted version?

[brickanalyst]

I have 3 table with same structure as shown below:

From top to bottom;
table1: we have 5 scenarios (name could be duplicated) with unique ids and we have multiple different variables with different data types.
var_1; cost of something (decimal)
var_2; cost of something else (decimal)
var_3; power (text)

var_4; difficulty level (text)

var_5; expected to report date (date)

(PS: There are around 50 variables)

table2: this is just the same but from a different team so ONLY prices are different.

table3: this is representing the same scenarios but we are looking at variances as a percentage for dollar costs, others are just same.

Okay, then here's my problem is coming

they want to see total var_1, var_2 and their variances, min value of variance_var_1, variance_var_2 and max
and their standard deviation of variance_var_1 and variance_var_2 

NOTE: I can show this view by creating calculated table using custom dax (switch case) and you can find the way here:
https://community.fabric.microsoft.com/t5/Desktop/Re-create-excel-view-in-power-bi-pivot-unpivot/m-p...

However, I can't do any filter because I have only aggregated columns that is summarized.
How can I filter by var_3, var_4, var_5 ?

For instance:
Filter by "250" POWER?
Filter by "Medium" level 

Filter by last 10 days

I'm stuck and I don't know how to get around. Any suggestions and ideas please let me know
Thanks in advance !!!

[Anonymous]

Thanks for the reply from lbendlin , please allow me to provide another insight:

Hi  @brickanalyst ,

Here are the steps you can follow：

1. In power query - add a custom column for each table containing the name of the current table.

=“Table1“

2. Append Queries – Three or more tables -- add

3. Transform – select [var_1] and [var_2] – unpivot columns.

4. Enter data – create a table.

5. Create measure.

table_1 =
IF(
    MAX('GaroupTable'[Group])="var_1",
    SUMX(
        FILTER('Append1',[Custom]="Table1"&&'Append1'[Attribute]="var_1"),'Append1'[Value]),
    SUMX(
        FILTER('Append1',[Custom]="Table1"&&'Append1'[Attribute]="var_2"),'Append1'[Value]))

table_2 =
IF(
    MAX('GaroupTable'[Group])="var_1",
    SUMX(
        FILTER('Append1',[Custom]="Table2"&&'Append1'[Attribute]="var_1"),'Append1'[Value]),
    SUMX(
        FILTER('Append1',[Custom]="Table2"&&'Append1'[Attribute]="var_2"),'Append1'[Value]))

variance =
[table_2] - [table_1]

table_3_minimum =
IF(
    MAX('GaroupTable'[Group])="var_1",
    MINX(
        FILTER('Append1',[Custom]="Table3"&&'Append1'[Attribute]="var_1"),'Append1'[Value]),
    MINX(
        FILTER('Append1',[Custom]="Table3"&&'Append1'[Attribute]="var_2"),'Append1'[Value]))

table_3_maximum =
IF(
    MAX('GaroupTable'[Group])="var_1",
    MAXX(
        FILTER('Append1',[Custom]="Table3"&&'Append1'[Attribute]="var_1"),'Append1'[Value]),
    MAXX(
        FILTER('Append1',[Custom]="Table3"&&'Append1'[Attribute]="var_2"),'Append1'[Value]))

6. Result:

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[brickanalyst · Accepted solution]

Thank you @lbendlin and @Anonymous 

I'd like to share my answer on this case how it works.

1) I'm keeping unpivoted versions and creating another query and pivoting the all columns as attribute | value columns.
2) I'm adding another column to identify which table it is table_type (text)
3) I'm using addcolumns, iserror functions to get the value if it's numeric and creating in a temp table
4) Lastly, using required aggregation functions sumx, minx, maxx etc.
5) Magic: connecting pivoted table to unpivoted (original ver.) so you will have 1-many relationship, when you use any dimension column from original table you can filter the data you want in the "pivoted" version.

That's all. 

Anyone has any issue that is similar to this case? Use this approach.

[brickanalyst]

@lbendlin Do you mean unpivoting the variables that I want to do aggregations, append three tables together into one, that means one column = Variable, one column = Values... and How am I going to catch the value from the table I want?

[lbendlin]

you need to add the table name as a third column

[brickanalyst]

@lbendlin 

Appended Yellow Calc =

VAR _table =

    ADDCOLUMNS(

        'Appended Table',

        "IsNumeric", IF( ISERROR( VALUE('Appended Table'[Value]) ), 0, VALUE('Appended Table'[Value]) )

    )

RETURN CALCULATE(SUMX( _table, [IsNumeric] ), FILTER('Appended Table', 'Appended Table'[Table_Type] = "Yellow"))


I unpivoted tables, so I have variable and value columns, I catch the values if they are numeric, but it seems like filtering it's not working. Can you give me any suggestion?

[brickanalyst]

Okay I solved that with this part: 

Appended Yellow Calc =

VAR _table =

    ADDCOLUMNS(

        'Appended Table',

        "IsNumeric", IF( ISERROR( VALUE('Appended Table'[Value]) ), 0, VALUE('Appended Table'[Value]) ),

        "Table Type", 'Appended Table'[Table_Type]

    )

VAR _measure = SUMX( FILTER(_table, [Table Type] = "Yellow" ), [IsNumeric] )

--VAR _result = CALCULATE(_measure, 'Appended Table'[Table_Type] = "Yellow")

RETURN _measure

I will post back here later!!

[lbendlin]

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).

Do not include sensitive information or anything not related to the issue or question.

If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

Please show the expected outcome based on the sample data you provided.

Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447...

[lbendlin]

Unpivot your source tables to bring them into a usable format. Then append them and load them into Power BI.  Let the data model and DAX do the rest.