Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All
I am complete noob to PowerBi but i need to generate some dashboards urgently from our AD for a project i am working on.
I have created a table that has an abreviation for each city and its full name.
Code Location
LON London
Then i have pulled in the data i want from AD into a table, however location can only be measured by the OU the device is in. So the only way i can identify a devices location is by for example the Distinguished Name field where OU=LON.
So the dashboard needs to pull the live data from Active Directory and then do a look up on the device OU to match the location code against the partial string of the Distinguished Name field. So the output is
Device Location
Server1 London
I also then want to populate another table with the number of devices in that location.
Location. Devices
London. 50
I appreciate these are very noob questions and your help would be greatly appreciated.
Thank you for your help
Solved! Go to Solution.
Hi @djj2,
In your scenario, the key is to extract the city code from the DistinguishedName column to a new column, and create a relationship between the two table with city code column, then you can easily show the expected result on the report.
1. Use the Split Column by Delimiter option to split the DistinguishedName column in Query Editor.
2. Remove other columns, only keep the column(DistinguishedName.3) which contains the city code.
3. Add a new custom column to remove "OU=" from the code value.
=Text.RemoveRange([DistinguishedName.3],0,3)
4. Remove "DistinguishedName.3" column.
5. Click "Close&Apply".
6. Create a relationship between the two table with city code column.
Then you should be able to easily show the expected result on the report.
Here is the sample pbix file for your reference.
Regards
Hi @djj2,
So the dashboard needs to pull the live data from Active Directory and then do a look up on the device OU to match the location code against the partial string of the Distinguished Name field. So the output is
Device Location
Server1 London
Could you post some sample/mock of the device OU and the Distinguished Name field, so that we can better assist on this issue?
Regards
@v-ljerr-msft thank you so much for for coming back to me on this, I feel i didnt explain that very well so i should be clearer.
Here is exactly what i am trying to achieve for my entire project. I have to do an audit of the old objects in AD by location and generate a dashboard the owners can interact with. I have a little bit of knowledge on doing this but i need some guidence.
The field DistinguishedName contains the OU that indicates the location in one table. Please note that there is hundreds of thousands of entries in these tables.
| ||||||
I want to create a query (by merge i think) on this table that does a look up on the DistinguishedName matches the OU=LON entry and then match each row to another table that has the following.
Region | Country | City | Code |
EMEA | UK | London | LON |
Tables that I need to create from this are:
Servername | Location | Country | Region |
LONSERVER1 | London | UK | EMEA |
With another table that counts the entries:
Server Count | |
EMEA | 50 |
Uk | 20 |
London | 3 |
And a finally table that looks at the LastLogonDate that is older than 1st Jan 2013.
Name | LastLogonDate |
Server2 | 17/09/2001 |
That is all of the problems i have to solve to create my entire project. From these scenarios i can then do all of my other requests I have 🙂 Really excited to learn PowerBI, i have done so much trauling through forums and cant seem to find the right approach., but i'm also a complete noob and Trial and error isnt going as well as expected.
Thank you so much for any help of guidence you can provide.
DJJ2
Hi @djj2,
In your scenario, the key is to extract the city code from the DistinguishedName column to a new column, and create a relationship between the two table with city code column, then you can easily show the expected result on the report.
1. Use the Split Column by Delimiter option to split the DistinguishedName column in Query Editor.
2. Remove other columns, only keep the column(DistinguishedName.3) which contains the city code.
3. Add a new custom column to remove "OU=" from the code value.
=Text.RemoveRange([DistinguishedName.3],0,3)
4. Remove "DistinguishedName.3" column.
5. Click "Close&Apply".
6. Create a relationship between the two table with city code column.
Then you should be able to easily show the expected result on the report.
Here is the sample pbix file for your reference.
Regards
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
96 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
102 | |
88 | |
63 |