Making the result of a column dynamic by the filter of the user

[Crissceron]

Hi! I've created the "table 2" using data from the "table 1", (please see the pictures as examples).

the column "total hras" in table 2 is the result of the next code:

SWITCH(TRUE();
'tabla2[Actividad]="Ordenar";SUM('tabla1'[Ordenar]);
'tabla2[Actividad]="comprar";SUM('tabla1'[comprar]);
'tabla2[Actividad]="vender";SUM('tabla1'[vender]);
'tabla2[Actividad]="estudiar";SUM('tabla1'[estudiar]))

Then, with the table 2 im creating visualizations. My problem is that when im using filters from table 1 (for example dates) that visualization that was made from table 2 is static and is not getting filtered.

¿¿How can i make this visualizations dynamic using  filters (dates from table 1)  if the table 2 it was created on static mode????

- Please note that the column headers in table 1 are rows in table 2 and i dont have dates in table 2.

Has anyone had the same problem?..

Thanks for your help!.

[Crissceron]

@v-yanjiang-msft  thats a good solution... but now i have a problem using dates inside the visualizations (using dates as axis of a chart). let me show you a picture. (All the columns have the same values and thats not correct)

as you can see, the values are correctly without any filter, but definitely if i show the values for example by month, the columns of the chart are not getting filter.

for other hand... i can filter the chart using filters by the user. (please see the image below)

But im not looking for this, i would like to show this chart in panoramic view (more than 1 month or year) like the first image but with the correct values.

I created the measure with your solution. 

any solution for the correct visualization of the data in charts??

[v-yanjiang-msft]

Hi @Crissceron ,

If you want to show it in a column chart, you can modify the formula like this:

Total hras =
IF (
    ISFILTERED ( Table1[Date] ),
    SWITCH (
        MAX ( 'Table2'[Actividad] ),
        "Ordenar",
            SUMX (
                FILTER (
                    ALLSELECTED ( Table1 ),
                    'Table1'[Date] = SELECTEDVALUE ( Table1[Date] )
                ),
                'Table1'[Ordenar]
            ),
        "comprar",
            SUMX (
                FILTER (
                    ALLSELECTED ( Table1 ),
                    'Table1'[Date] = SELECTEDVALUE ( Table1[Date] )
                ),
                'Table1'[comprar]
            ),
        "vender",
            SUMX (
                FILTER (
                    ALLSELECTED ( Table1 ),
                    'Table1'[Date] = SELECTEDVALUE ( Table1[Date] )
                ),
                'Table1'[vender]
            ),
        "estudiar",
            SUMX (
                FILTER (
                    ALLSELECTED ( Table1 ),
                    'Table1'[Date] = SELECTEDVALUE ( Table1[Date] )
                ),
                'Table1'[estudiar]
            )
    ),
    SWITCH (
        MAX ( 'Table2'[Actividad] ),
        "Ordenar", SUMX ( ALLSELECTED ( 'Table1' ), 'Table1'[Ordenar] ),
        "comprar", SUMX ( ALLSELECTED ( Table1 ), 'Table1'[comprar] ),
        "vender", SUMX ( ALLSELECTED ( Table1 ), 'Table1'[vender] ),
        "estudiar", SUMX ( ALLSELECTED ( Table1 ), 'Table1'[estudiar] )
    )
)

Best Regards,
Community Support Team _ kalyj

[v-yanjiang-msft · Accepted solution]

Hi @Crissceron ,

Firstly, the value of a calculated column can't change with slicers, it's fixed. And the formula should be related with selected rows in Table1.

Here's my solution, create a measure.

Total hras =
SWITCH (
    MAX ( 'Table2'[Actividad] ),
    "Ordenar", SUMX ( ALLSELECTED ( 'Table1' ), 'Table1'[Ordenar] ),
    "comprar", SUMX ( ALLSELECTED ( Table1 ), 'Table1'[comprar] ),
    "vender", SUMX ( ALLSELECTED ( Table1 ), 'Table1'[vender] ),
    "estudiar", SUMX ( ALLSELECTED ( Table1 ), 'Table1'[estudiar] )
)

Get the result.

I attach my sample below for your reference.

Best Regards,
Community Support Team _ kalyj

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

[parry2k]

@Crissceron you dont need to create a table2, first you unpivot table1 

Transform Data -> Select 1st 3 columns -> Right CLick -> Unpivot other columns.

It will give you two new columns, attribute and value, rename these if you want, close and apply.

and now in matrix visual, use column on rows you want, attribute on columns, and value on values, this will get you what you are looking for and you can also filter on the date. You can also use other visuals to get the output you need. The core here is to unpivot your table.

✨ Follow us on LinkedIn and  to our YouTube channel

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make effort to give Kudos to whoever helped to solve your problem. It is a token of appreciation!

⚡ Visit us at https://perytus.com, your one-stop shop for Power BI-related projects/training/consultancy.