Filter for Calculated column

[Kolumam]

Hi All,

In the below table, Y1, Y2.....Y6, Cumulative are calculated columns. How can I use them as filters so if I click on Y1, it will show the numbers only for Y1. Currently Y1 is a calculated column.

[Zubair_Muhammad]

hI @Kolumam

I can think of this technique.

Assuming your Table Name is Table1

First create a Calculated Table from the Modelling Tab>>New Table

Parameters =
DATATABLE (
    "Index", INTEGER,
    "MyColumns", STRING,
    {
        { 1, "Y1" },
        { 2, "Y2" },
        { 3, "Y3" },
        { 4, "Y4" },
        { 5, "Y5" },
        { 6, "Y6" },
        { 7, "Cumulative" }
    }
)

Now create a MEASURE

Measure =
VAR selectedcolumn =
    SELECTEDVALUE ( Parameters[Index] )
RETURN
    SWITCH (
        TRUE (),
        selectedcolumn = 1, SUM ( Table1[Y1] ),
        selectedcolumn = 2, SUM ( Table1[Y2] ),
        selectedcolumn = 3, SUM ( Table1[Y3] ),
        selectedcolumn = 4, SUM ( Table1[Y4] ),
        selectedcolumn = 5, SUM ( Table1[Y5] ),
        selectedcolumn = 6, SUM ( Table1[Y6] ),
        SUM ( Table1[Cumulative] )
    )

[Zubair_Muhammad · Accepted solution]

@Kolumam

Please see attached file.

[Zubair_Muhammad]

@Kolumam

To fix the sorting problem...

Select the Parameter Table

Select MyColums

Go to Modelling Tab>>Sort by Columns>>select Index

This is shown in picture below