so .
Properties yeah yeah so we use oh yeah i use obs for the live stream and we have audio now well thank you for letting us know folks that was great now uh what i'm trying to see is can they no they can't hear if somebody's on the call either so let me fix that.
All right so we should be good i was trying to say hello to you and i was like why is everybody so rude today you weren't rude uh let's see who's here mathias i did say hi to you earlier but you didn't hear me kendall is here good morning from america's georgia um yep no audio uh we fixed that i think let us know let us know yeah that would be embarrassing like how many times can i go through hellos elaine says bonjour from quebec canada rakesh is here hey rakesh is one of our.
Newest members in the pro plus program hello good to see you rakesh um and um ram yep ram is here ivan is here steve yes you should be able to hear me mike j talker hi from calgary okay cool awesome very much audrey neil dixon from uk hi from norada india uh cool so guys i'm gonna have to repeat my warning so by the way you know what let me kick this off and then i'll i'll do the personal stuff in just a little bit i'll keep short but i think i i do need to share it so we're here for.
And we have let me see we have dawn and hans today and of course robert is our moderator and and i didn't give him enough prep time but we're gonna do our best so the idea is that he's the dj he's gonna spin the music and i'm gonna be on the dance floor for you guys answering questions yeah not dancing today so um so yeah so he's gonna be moderating as always keep an eye on chat so.
Uh if you want your questions queued up be nice to robert that's the short version uh so yeah so uh we're gonna get started here so robert so folks check in with robert on if you submitted a question and you're here uh put it put it in the chat and i think we did hear from hans uh and uh don if you submitted a question then let robert know and frankly for our students.
Hey we love them so even if you didn't pre-submit the question then do let us know by the way once you do submit it you can it kind of looks like this and you can actually click on that and go on the board and you can see everything that's been submitted and everything that we discussed in the last sessions as well cool so to so um put it in in chat box if you submitted a question and robert is gonna kind of uh cue them up here so.
I'm gonna start with hans and i'm supposed to take a i supposed to do something here so um yeah cool let's go answering oh i'm supposed to have an answered okay hold on guys uh so january 29th 20 2021 done it has to be a better way to do this great oh.
Oh i get it okay so robert is going to queue it up for me for those folks who did submit questions uh and i'm going to start with this one oh i know what i'm supposed to do i don't know if this is going to work let's try it that looks like an interesting one let's go over here and.
Posts Related:
And there we go all right so the first one is link the user to the
Security group membership without using ms graph api cool and let's so folks yeah so you see the question up top let me see if i can read the details off the screen we're exposing reports from premium workspace to our customers love that scenario to avoid making a dedicated report for each customer we implemented rollable security but as we have more.Than 100 customers and a security demanded to work with a dedicated security group for each customer okay that makes sense we needed to group all security groups underneath one parent security group what i need to make what do i need to make the power bi roller security work and to know as to which security group the authenticator user belongs to makes sense so this means that the power bi should be able to verify the aed.
Yeah hans i'm not sure i would have a great solution for you because frankly the best one that i had you mentioned it at the end of the script which is you're basically replicating it so talk to me what's going on actually what is an spof help me uh it's not a point of failure yeah that makes me a single point of failure because i need to run locally well let's let's take the scenario step by step so we have we are using entitlement packages.
We expose those entitlement packages to our external uh parties whenever there is somebody uh admitting through that entitlement packages they are created in our as a guest account in our azure active directory yes all working fine they are populated inside the dedicated security group that is working fine but for my row level security to work i need to know which authenticated user is member of which security group.
And that is an information i cannot obtain through the power bi service because the only way that i found today to um to connect and to use that information is by using the api ms graph okay but this is an overkill for our security and i'm doubting that's the only way to um to know which user is doing activity on our power bi service because you have the power bi uh.
Report which tells us who uses which report yeah and that i believe is also linked to azure active directory well so who is easy right you know you use the username function in power bi and that gives you who is yes but you're not that that is what you can do here you are asking oh and which security groups to belong to and yes yep i don't know anything built into power bi i didn't even know ms graph api so hey i i definitely don't know the direct.
Answer to that so folks i'm going to give a shout out first on youtube and and say hey does anybody know of a better way to extract the security group information actually because of my solution today options there is ms graph api but yeah and and then there's a powershell script and that's that's a separate one right so what about well so um what is also bizarre for me is that when you create a new power bi report.
And you say get data you have a connector available of the shelf that is allowing you to query your local active directory that is available really but there is no connector available for the azure active directory and the only way today also to make it work is asking our administrators to copy everything from the azure active directory into our local active directory.
And that is not allowed because then all external member would also be inside our local active directory in outlook uh this is not the way they should work it should work the other way around and i'm i'm guessing that's maybe power bi is is working and on that because i upvoted one of the solutions to make also azure active directory available but maybe there is another way because.
You know you can use an http request and with all data feeds extract some
Information but not the one that i need i can have just an an output of of security groups that i can access but not the membership that is not allowed and i believe it's not really a problem with power bi it's more of course related to uh security settings but my question was more is there anybody else that has yeah that same issues and maybe.Found the solution except the one that i'm doing now power bi script that i run and then i populate the outcome of the membership into my back end and then of course power bi can consume yeah let me let me check in with robert really quick so robert don't worry about the admin rights i'm gonna i'm gonna do the dj on the trello uh but you can help us just keep the names so just keep a list of the names and especially like if sometimes students join on youtube we you know we love them too.
So so yeah and maybe put a marker and um and yeah you can just queue it up it's like hey abby take uh you know dawn's question next or something and and then i'll do that part um so yeah so i'm not seeing any answers on youtube yet but who knows i don't know maybe somebody's going to watch in the replay and have something i will say that this is definitely a moment where all the you know all the hateful things that i do to it people i mean not really but you know i do like.
To throw them under the bus so many times it's coming back to bite me because uh this is one instance where yeah i had just somebody in i.t i i didn't know how they did it i just got the security group information in a sql table i don't know how they were doing it but they were somehow doing a sql table and yeah that was also one of the proposals that i did security yeah though so if they can repopulate the membership into an azure sql server.
Instance then that can also be uh queried from the power bi service but it is an additional request they need to maintain that so and it should be it should be possible for a power bi service from microsoft to extract the data from an azure active yes service from microsoft in the same tenant it makes sense to me that it should be available i mean imagine if just like a username function there was a user groups function right.
I mean how easy would that be he said yeah username user groups easy really easy now i would definitely encourage like a lot of times i have these ideas like why isn't this darn thing this way and and and i'm too either too lazy or too demotivated to submit an idea but don't do what i do don't i would say definitely submit that idea to ideas.powerbi.com saying hey add a function give me a user groups function and and and i'll guys really quick talking about the ideas.powerbi site sometimes i'm demotivated because.
It feels like a popularity contest and you know and i'm like man i mean how many people are going to vote for my idea and i think it's important but i don't think i'm going to get like a thousand votes but sometimes you can get lucky as well sometimes they pick up something and they in their wisdom they say you know what that that part is important and and and of course what we have seen is depending on where the focus is like i know in one phase they were really focusing on uh like large-scale clients so if you had a.
Feature like this one i think it's going to be great for large scale clients then they were really picking it up so yeah that's the best thing that i can say for the user groups function yeah so sorry sorry hans i can't help you beyond that because again and i mean how the i.t person did it that was a black box to me i'm not seeing any new ideas on youtube uh but yeah um and hopefully this this question might trigger somebody uh or yeah and hey somebody who has a solution if you can if you can submit an idea kind of.
Right now put that link in the chat box i mean everybody listened to it i made a case for it right i mean why not why not have user groups functions put that in the chat window and say hey folks vote for it and at least you'll get the thing going so yeah you know that's the best i can offer all right cool uh thank you so folks uh um i don't know maybe i should count strikes and you know uh what's the what's the other one i'm not a baseball person home run i know what a home run is.
Okay so that was the first one and i only hesitatingly drag it into the done column has it done for me uh let's take the next oh wait is that robert did you figure it out there's something seems queued up for me from graham yes yes i can get it out yeah wow hey so guys again if you want your questions queued up be nice to robert actually hey you should be nice robert even.
Otherwise right so there's no reason not to be nice to robert but uh it might help you uh so let's let's look at the next one so i'm gonna get over here oops and passing values from a power bi dashboard into power query oh boy i'm a little nervous let's see so this is from graham gourlay from curriculum village hampshire united.
Kingdom awesome oh boy i'm working on a project to provide market where is you know why can't somebody just give me like a good meaty dax question okay hopefully there's some coming i'm working on a project to provide market insight for uk residential property great love that one of her students in fact worked for a real estate company that was exciting we saw the project oh um by the way.
Folks if uh um if if you are interested on the path sorry i mean maybe i should have done this at the beginning learn power bi if you are interested in the path to become a power bi consultant your time is running out my friends seriously because we have this awesome webinar on thursday january 21 thanks to everybody who participated and especially to those who participated in the mini events before that when did they bomb i mean if you were.
There you know what i'm talking about give me a shout in the chat and let me know it was crazy it was really small groups we had like 12 20 something but that meant a lot of interaction and we got deep into that stuff and i realized again that i love doing live sessions i love teaching live and again these since they were smaller groups they were a lot more interactive and we were just using zoom meeting none of this youtube live crap now youtube live is great so uh but yeah zoom meeting it was different.
So loved it and and yeah so so that was awesome uh but yeah it's closing soon so go catch that on learnpowerbi.combro it's only available for a limited limited time okay let's get back to the question um project inside for residential properties their companies provide required data through json apis cool i would like to be able to have a dashboard user select one or more parameters from a slicer and have these users okay i see where.
This is going you know so ram i want to hear from you so again when people switch from one tool to the other you have to unlearn some things and and i don't know i'm not saying like this is an example of that but so the scenario you're describing where something is passed in as a query parameter i often see so i know your scenario you're.
Moving this from an excel you're saying i'm coming from an excel report where i could do it this way to power bi but i often hear the scenario from folks who use sql server reporting services anybody who is or has used reporting services sql server ssrs let us know give us a shout out in the chat window and what i'm talking to those folks they are always yeah they always talk about this they talk about um they talk about that hey.
How can i pass a query parameter but frankly that's a very ssrs thing and i encourage them to let go of that power bi doesn't work that way power bi is is a cube technology really right it's a cube technology and it just wants to pull in all the data and then you don't need to do any of these song and dance with query parameters i'm going to bring in all data and then if you select on a slicer like oh i don't only want united kingdom.
Then you click on the slicer and it filters it down right so so that's the approach so i know it's not like directly answering a question but generally i really think that that's the best practice that's the way you should go so let us know in the chat why why that isn't a great option why wouldn't that work yeah william is here and ssr is dev good to have you on rakesh had a shout out earlier for our mini mini event rocket was actually one of the stars on those shows.
We we had some of these uh some of the members come on and um yeah they they they grabbed the mic and uh it was awesome so yeah rakesh thank you for being there um so yeah so graham i want to hear from you but why that solution does it not entirely does it does it make sense like yeah cool i'll do that that's a power bi way but let me read ahead because i had kind of jumped to the answer or my answer so he's saying i found some information.
Now we cannot get to work so we have a little bit of dynamic let's create power query api calls yeah man that's that's stuff that i haven't gotten into either a dynamic api call actually i don't know how how it can work because frankly querying isn't dynamic oh um like you know in a typical model so guys there there's the what is it called direct query right yes direct query um i don't use direct query.
As much very rare for me the default is import by default i import does anybody know why why is that my default um right so um so definitely in the default import mode you can't have a dynamic parameter it's just right i mean the the refresh isn't dynamic it's it's not dynamic right i mean you can uh you can schedule it you can manually refresh but that's it so the only hope you have.
Of getting a dynamic dynamic query run with a parameter in this case is direct query and yeah i mean i don't i don't really know got the answer on how to make that so you say you can't you you're trying to use uh cross join work that's a great blog you can't get it to work yeah i don't think we're going to be able to help you with a specific error.
I yeah yeah um okay cool so let me let me check it again i'm hoping you took my option okay i don't see the response there so graham yeah i mean i mean i really i don't i don't i have never come across a model from not just my own model or any of the consultants that i've worked with which use that approach where it passed back a query to get a dynamic result you know you just query everything and.
Then actually you know what yeah direct query you don't have to worry about the parameter at all actually i think i answered my question so think about it it can never ever work in the import model and direct query frankly does it for you in direct query if you click on a slicer it changes the query and sends it back right so direct query is is is doing it all the time you know that's kind of funny i didn't think about it that way so if you direct query does it all the time by default anyway you can't stop it you can.
Not have it pass in parameters and import there's no way to do it so i'm not sure if that's good news and bad news what combination is that all right cool so um oh god i'm i'm feeling kind of bad i'm feeling like i started with two non-answers um uh okay cool so let's see uh let's move on to the next one oh uh uh avi uh yeah some student disney that uh.
I've seen somebody say that they still see the first question on the screen so i don't know whether that's an issue with everybody oh that's interesting yeah i did uh switch that passing values from barbie dash wouldn't do query oh okay we'll let's see um maybe i'll check in again so answering cool we got that and we got i learned probably a student oh don did that have a question great.
Hey robert by the way you know this is working far better than any of my previous q a sessions so this is awesome come on guys show some love for robert all right so what you should be seeing at the top is the next question which is date comparison between two related tables you know so i'm i'm i'm getting a good feeling about this i.
May you know be proven wrong but i'm getting a good feeling about this so let's see so don uh you're on the phone right go ahead and unmute yourself if you like actually yeah that would be cool awesome i don't see my question on the screen though oh so i think the folks um am i sharing oh i see yeah yeah oh sorry yeah so i use an obs trick for the question so you if you're watching on zoom.
