Getting selected Month from a slicer

[yousef_alsalem]

Hi,
I have issue with getting the selected value from a drop down list slicer.
I have a mesure in the Months table to catch the select value in the slicer

SelectedMonth = SELECTEDVALUE('Months'[MonthName])

I want to count the joiners in Joiners table > EmpCount and list them in a table

But this is not working

EmpCount =

var mon = Months[SelectedMonth]  //  VALUE('Months'[SelectedMonth]) //  VALUES(Months[MonthName]) All not working

VAR data =

    CALCULATE(

        COUNT(MainData[Employee Id]),

        FILTER(

            MainData,

            MainData[Business Unit] = Joiners[Business Unit] &&

            MainData[Hiring Month] = mon

        )

    )

RETURN data

To test the code, this is working

EmpCount =

VAR data =

    CALCULATE(

        COUNT(MainData[Employee Id]),

        FILTER(

            MainData,

            MainData[Business Unit] = Joiners[Business Unit] &&

            MainData[Hiring Month] = "December"

        )

    )

RETURN data

 

Please help me with this.

File : Turnover_dummy.pbix 

[Fowmy]

@yousef_alsalem 

Have you created the required relationship between the tables?

Sharing a dummy Power BI file representing your scenario would be beneficial. You can save the Power BI file on Google Drive or any other cloud storage platform and provide the link here. Kindly ensure that permission is granted to open the file.

[yousef_alsalem]

Hi,

Linking the table in a relationship is not what I want, because this is part of a bigger project and some tables have no realtions. In the next step I will have to add more filter, like by year and quarter etc.

I have added a dummy file at the end of my post.

Thank you

[ERD]

Hi @yousef_alsalem ,

- make sure you use 'Months'[MonthName] in the slicer

- try rewrite the measure:

EmpCount =
var mon = SELECTEDVALUE('Months'[MonthName])
VAR data =
    CALCULATE(
        COUNT(MainData[Employee Id]),
        MainData[Business Unit] = Joiners[Business Unit],
        MainData[Hiring Month] = mon
    )
RETURN data

[yousef_alsalem]

Thanks for the reply but it give me an error, cannot find name Business Unit

The expression contains columns from multiple tables, but only columns from a single table can be used in a True/False expression that is used as a table filter expression.

I'm sure that I use 'Months'[MonthName] in the slicer
I belive that my code main issue in getting the selected value from the slicer not filtring the data.

[ERD]

Oh, sorry, indeed, it will give an error since it's a measure. Try this:

EmpCount =
var mon = SELECTEDVALUE('Months'[MonthName])
VAR data =
    CALCULATE(
        COUNT(MainData[Employee Id]),
        MainData[Business Unit] = SELECTEDVALUE(Joiners[Business Unit]),
        MainData[Hiring Month] = mon
    )
RETURN data

[yousef_alsalem]

Theres is no slice for the business unit.
In the next step I will have to add more filters, like by year and quarter etc.

I have added a dummy file at the end of my post.

[ERD]

Actually there is, and you can see it on your screenshot:

[yousef_alsalem]

It gives no result

:1023034i5BA7A9B07F2833B1:

[ERD · Accepted solution]

Did you create a measure? From your file: 

Your screenshot shows a summarized column, not a measure.

[yousef_alsalem]

Thank you it's working now

EmpCountMeasure =

var selectedMonth = SELECTEDVALUE('Months'[MonthName])

var selectedYear = SELECTEDVALUE('Years'[Year])

var data =

    CALCULATE(

        COUNT(MainData[Employee Id]),

        MainData[Business Unit] = SELECTEDVALUE(Joiners[Business Unit]),

        MainData[Hiring Month] = selectedMonth,

        MainData[Hiring Year]= selectedYear

    )

RETURN data