Forum Discussion
Helper IHow can I create a user table with dax ?
Hello,
Here is my problem : I have a huge table in direct query with a column named "name_user" it contain a dozen of differents users.
I want to create role that match their "name_user" with the value that the USERNAME() function is sending.
But in order to do that, i need to create a dimention table of user like this :
user id ; user name (that refers to mu "name_user" ; the result of the USERNAME() function.
I will fill up the table because i know what is the username() of the different "name_user"
And then i'll be abble to create roles to filter my table in function of their username.
So my question is : how do I create a table just with 3 columns ? I've tried to follow the documentation but there is always some errors in my code. Maybe an exemple would help me a lot.
Many thanks.
Hi paulineCom ,
Can you give a little bit more information please? What is the syntax you are using and the information you want to place in the table.
To creata a table in dax you have several options but this depends on the way you want to build you can do:
Table2 = ADDCOLUMNS ( DISTINCT ( 'Table'[Name] ); "E-mail"; 'Table'[Name] & "@gmail.com"; "Role"; SWITCH ( 'Table'[Name]; "A"; "MANAGER"; "WORKER" ) )This table uses the distinct values of Name column in another tables and add two additional tables one with e-mail and another with role based no the name of the person
You can also create a table with a single column:
Table2 = DISTINCT ( 'Table'[Name] )And the add each individual column:
E-mail = 'Table2'[Name] & "@gmail.com" Role = SWITCH ( 'Table2'[Name]; "A"; "MANAGER"; "WORKER" )You can create a table with all values written direcftly in the code:
Table 3 = UNION ( ROW ( "Name"; "A"; "E-mail"; "[email protected]"; "Role"; "Manager" ); ROW ( "Name"; "B"; "E-mail"; "[email protected]"; "Role"; "Worker" ); ROW ( "Name"; "C"; "E-mail"; "[email protected]"; "Role"; "Worker" ) )There is a various number of ways to build this, I know I have been very generic but without any sample data is difficult to pin point what is happening in your model.
3 Replies
- MFelix
Super User
Hi paulineCom ,
Can you give a little bit more information please? What is the syntax you are using and the information you want to place in the table.
To creata a table in dax you have several options but this depends on the way you want to build you can do:
Table2 = ADDCOLUMNS ( DISTINCT ( 'Table'[Name] ); "E-mail"; 'Table'[Name] & "@gmail.com"; "Role"; SWITCH ( 'Table'[Name]; "A"; "MANAGER"; "WORKER" ) )This table uses the distinct values of Name column in another tables and add two additional tables one with e-mail and another with role based no the name of the person
You can also create a table with a single column:
Table2 = DISTINCT ( 'Table'[Name] )And the add each individual column:
E-mail = 'Table2'[Name] & "@gmail.com" Role = SWITCH ( 'Table2'[Name]; "A"; "MANAGER"; "WORKER" )You can create a table with all values written direcftly in the code:
Table 3 = UNION ( ROW ( "Name"; "A"; "E-mail"; "[email protected]"; "Role"; "Manager" ); ROW ( "Name"; "B"; "E-mail"; "[email protected]"; "Role"; "Worker" ); ROW ( "Name"; "C"; "E-mail"; "[email protected]"; "Role"; "Worker" ) )There is a various number of ways to build this, I know I have been very generic but without any sample data is difficult to pin point what is happening in your model.
paulineComHello thank you both for your help,
It's working thanks to the syntax MFelix gave me.
Have a nice day 🙂
- Pragati11
Hi paulineCom ,
Did you try selecting just 3 columns from your main table and creating your table?
You can do this easily in Query editor in Power BI.
- Richt click on your base table -> select REFERENCE (this creates a new table)
- select the columns you want in this table and delete others.
- Remove duplicate rows from your USER column.
- Create a relationship as required.
Thanks,
Pragati
