topic Re: Unable to get desired rows from parameterized function in DAX Commands and Tips

Unable to get desired rows from parameterized function

Asheesh — Mon, 26 Dec 2022 16:11:40 GMT

Dear All,

I am trying to return a row/ column from a parameterized function. Having below two data models -

Employee
Employee Id	Employee Name	Salary
1001	SAM	90,00,000.00
1002	John	70,00,000.00
1003	Rodrix	50,00,000.00

SalaryGrades
Grade ID	Min Sal	Max Sal
1	75,00,001.00	99,00,000.00
2	60,00,001.00	75,00,000.00
3	45,00,001.00	60,00,000.00

Below is the function which I created and using to add a new column -

//Function name GetSalGrade(InputSal as number) = (InputSal as number) => let #"SingleRowSal" = Table.SelectRows(#"SalaryGrades",each [Min Sal]>=InputSal and [Max Sal]<=InputSal), Source = #"SingleRowSal" in Source

I am passing the values as 76,00,000 OR 65,00,000 OR 50,00,000 and each time it is returning a blank table.

My desired data model should be like below -

EmployeeSalaryGrade
Employee Id	Employee Name	Salary	Employee Garde
1001	SAM	90,00,000.00	1
1002	John	70,00,000.00	2
1003	Rodrix	50,00,000.00	3

(Actually I will add a new column EmployeeGrade in the existing Employee Model)

Please suggest what wrong I am doing.

Re: Unable to get desired rows from parameterized function

AlB — Mon, 26 Dec 2022 16:32:25 GMT

Hi Asheesh

Small change to your function:

(InputSal as number) => let SingleRowSal = Table.SelectRows(#"SalaryGrades",each [Min Sal]<=InputSal and [Max Sal]>=InputSal), Source = try SingleRowSal[Grade ID]{0} otherwise null in Source

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Unable to get desired rows from parameterized function

AlB — Mon, 26 Dec 2022 16:33:42 GMT

Asheesh

See it all at work in the attached file

Please accept the solution when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Re: Unable to get desired rows from parameterized function

Asheesh — Mon, 26 Dec 2022 17:35:21 GMT

Thanks AlB for your swift response. It's working 😀