how to pass the filtered name from slicer into column name in measures and apply the filteration for

[NadeemAhamed]

Dear All,

Good Day.

I have attached the excel and pbix file for your reference. 

I want to apply the slicer value as a column name in measures. 

Ex: test=

var _mnth=FORMAT(SELECTEDVALUE('Month'[Month]),"MMM")

RETURN

CALCULATE(SUM(testing[Apr]),FILTER(testing,testing[Company Name]="A" && testing[Month]=_mnth && testing[Plan/Actual]="Actual")

)

* I want to apply the slicer value in the place of static column name as mentioned above measures. 

Excel: https://toyotsu.box.com/s/iz91ukipab73cncpfaqjawok3z04vsl1

Pbix: https://toyotsu.box.com/s/ewpl7qlo1n7xdi6mecghhfsfuuh883f4

Request: Kindly suggest any another table structure which will reduce the table rows. (Bcz this table will going to have data from April-March)

Thank you in Advance. 

[Anonymous · Accepted solution]

Hi @NadeemAhamed 

I downloaded and viewed your files. I realize that your problem may be that the slicer selection is not working for the measure.

This may have something to do with your table structure.

I noticed that you created the slicer using "Month", but in the matrix you put the values for each month.

In fact, the months in the slicer are not related to the months you have in the table. Therefore, the slicer cannot filter the table.

You can try the following:

Here's some dummy data

"Table"

Select the month column and click "Unpivot Columns".

The table is transformed into the following form, and you can rename it to "Month".

Click on "Close & Apply".

In the meantime, I have made some changes to your code.

Create a measure.

test = 

var _mnth=
    FORMAT(
        SELECTEDVALUE(
            'Table'[Month]),
            "MMM"
        )

RETURN

CALCULATE(
    SUM('Table'[Value]),
    FILTER(
        ALL('Table'),
        'Table'[Company]="A" 
        && 
        'Table'[Month]=_mnth 
        && 
        'Table'[Plan/Actual]="Actual"
        )
    )
 

Here is the result.

Regards,

Nono Chen

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