Filter table (Multiple Condition) using SELECTEDVALUE from slicer - Please Help!

[Anonymous]

Hi,

Need help in filtering table using SELECTEDVALUE from slicer.

"data" contains all information

Intention is to filter the information from data using the selected value from the slicer

The filter looks like this 

Table =

FILTER(data,data[First Yr of Service]<=SELECTEDVALUE(selected[Year]) && (data[Last Yr of Service]>SELECTEDVALUE(selected[Year]) || data[Last Yr of Service] = blank()))

When I ran that in DAX, nothing is returned

I tried replacing "SELECTEDVALUE(selected[Year])" with a value like "2010" , the code looks like this

Table =

FILTER(data,data[First Yr of Service]<="2010" && (data[Last Yr of Service]>"2010" || data[Last Yr of Service] = blank()))

and after running that I see results.

SELECTEDVALUE(selected[Year]) is working when I checked using the datacard

Really appreciate your help! Have been stuck on this problem for the past few days

[amitchandak]

@Anonymous , You can not use the slicer value in table creation.

[Anonymous]

Thanks for your speedy reply. Can you advise what I can do as an alternative?

[amitchandak]

@Anonymous , There is not alternate you can have this table in measure and use

Measure

var _Table =
FILTER(data,data[First Yr of Service]<=SELECTEDVALUE(selected[Year]) && (data[Last Yr of Service]>SELECTEDVALUE(selected[Year]) || data[Last Yr of Service] = blank()))

return

sumx(_table,[Column])

[Anonymous]

Hi Amit

Unfortunately I got an error message

"The syntax for 'RETURN' is incorrect. (DAX(FILTER(data,data[First Yr of Service]<=SELECTEDVALUE(selected[Year]) && (data[Last Yr of Service]>SELECTEDVALUE(selected[Year]) || data[Last Yr of Service] = blank()))RETURNSUMX(_Table,[Column])))."

Can you help? Sorry I am not very good in DAX

[amitchandak · Accepted solution]

@Anonymous , Try like

Measure =
var _Table = FILTER(data,data[First Yr of Service]<=SELECTEDVALUE(selected[Year]) && (data[Last Yr of Service]>SELECTEDVALUE(selected[Year]) || data[Last Yr of Service] = blank()))
return
sumx(_table,[Column])

[Anonymous]

Hi Amit,

Unfortunately I am still seeing error message: "Column 'Column' cannot be found or may not be used in this expression." Can you help?

[amitchandak]

@Anonymous , This you have use a column from your table

sumx(_table,[Column]) as I am not aware of name, I suggested that

like  this can be

countx(_table,[employee_id])

[Anonymous]

Thanks a lot Amit!!! 

I noticed the "measure" method can only return a number. If I would like to create a chart to show count of male of female employees who were employed using the same filter 

FILTER(data,data[First Yr of Service]<=SELECTEDVALUE(selected[Year]) && (data[Last Yr of Service]>SELECTEDVALUE(selected[Year]) || data[Last Yr of Service] = blank()))

How do I create a table for that? 

[Anonymous]

Sorry my bad, I figured how to do it...

Thanks a lot for your excellent support Amit!!

[amitchandak]

@Anonymous , Thanks for letting us know. In case it is not resolved. Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Also, Check if my HR blog with the Hire and termination date can help.

https://community.powerbi.com/t5/Community-Blog/HR-Analytics-Active-Employee-Hire-and-Termination-trend/ba-p/882970

https://www.youtube.com/watch?v=e6Y-l_JtCq4