topic Re: Filter an entire table based on What If Parameter and Logged In User Name in DAX Commands and Tips

Filter an entire table based on What If Parameter and Logged In User Name

MohitPowerBI — Mon, 08 Jul 2019 14:50:29 GMT

Hello,

I am trying to filter the data based on logged in user and a cutom filter(slicer) which I created using What If Parameter of type Whole Number. I used below DAX to convert Whole Number Parameter to a string type slicer ( for end users)

Projects = SWITCH(TRUE(), Projects[Projects Value] = 1, "My Projects Only", Projects[Projects Value] = 2, "All Projects")

Now, I want to use this slicer to filter the data from Project table.

If User selects 'My Projects Only', It should display Projects where 'PROJECT_MANAGER = Logged in User' , otherwise it should display all rows.

Any ideas on how to achieve this in Power BI/DAX are much appreciated!

Thanks,

Mohit

Re: Filter an entire table based on What If Parameter and Logged In User Name

Anonymous — Mon, 08 Jul 2019 18:23:37 GMT

Project Visible :=
var __selection = SELECTEDVALUE( Projects[Project Value] )
var __currentUser = USERNAME()
var __oneProjectVisible = HASONEVALUE( TheProjects[Project_ID] )
-- ManagerUserName must be in the format of __currentUser
var __currentProjManager = VALUES( TheProjects[ManagerUserName] )
return
	if( __oneProjectVisible,
			
		if(
			__selection = 1, -- my proj only
			__currentProjManager = __currentUser,
			TRUE() -- show all rows
		),
		
		-- don't show anything if many projects
		-- are visible in the current context.
		FALSE()
	)

-- The table with projects, TheProjects, must
-- have a column that stores your managers'
-- domain-name\user-name identifiers.
-- You can filter your table in a visual using
-- the above measure. If TRUE(), show the row,
-- if FALSE() hide.

Best

Darek

Re: Filter an entire table based on What If Parameter and Logged In User Name

MohitPowerBI — Tue, 09 Jul 2019 20:59:08 GMT

Thank you Darek, it worked like a charm :)