Suggestions. If this is greyed out for you, it can be for different reasons. Number one, this is still a preview feature, so you will have to enable that in the options. I'm going to go to File > Options and Settings > Preview Features and make sure you have a checkmark here. Number two, your admin also needs to enable this in the admin portal under Tenant Settings. So they will have to go here, scroll all the way down, and they're going to see Quick Measure Suggestions. This.
Part has to be enabled so "Allow Quick Measure Suggestions." The second thing that needs to be enabled if you're outside the US is the "Allow User Data to Leave Their Geography." The machine learning model is currently only deployed to US data centers. If your data is outside the US, this feature is going to be disabled by default unless your tenant admin enables it. Measure suggestions are based on OpenAI's GPT-3 model, so one of the first implementations of.
Microsoft OpenAI partnership, and it's available in preview since October 2022. The advantage of using this box here instead of going to ChatGPT is that Power BI has access to your data model, and it can generate the DAX code with your table and column names so that you can use it straight away without tweaking it. Right, so at least that's the theory. Let's see what happens in practice. Okay, so we have a prompt here that tells us we can use natural language to describe the measure.
That we need, like "Total Sales for Canada this year." This sounds very promising, but before I start to do more complicated stuff, I just want to get familiar with the interface and see how this whole thing works. So, I'm going to ask it something really easy: I just want to get "Total Quantity." Basically, it just needs to give me the sum of the Quantity column. Let's generate. Okay, so there is something popping up here. We get a preview result and also our calculation, which is.
A thousand separator. So, I'm going to update the formatting, go with "Whole Number," and add a thousand separator to it. Notice, this keeps updating all the time. If you want to get rid of this, just click on the "X". Here, I'm going to add a table so we can take a look at what we just added. Let's put in "Month" and "Year" combination and our new "Total Quantity" measure. Okie dokie, so let's zoom in so we can view our numbers better..
Now that we know how this works, let's try something a bit more complex. I want to get total sales value. So, notice here in my sales data table, I don't have a column that we can sum to get the sales value. We need to multiply "Price" with "Quantity", but we have to do this row by row. So, we have to use the SUMX function. Well, let's assume I don't know that. All I know is that I want to get my total sales value. Yeah, that's what I want to have. Let's generate that..
Oh, it can't generate it. Well, what if we be a bit more specific? That we wanted to calculate our total sales value. Yeah, no, it can't do that. It doesn't know what it needs to do. And, okay, yeah, this is a bit tricky because we don't have anything whole sales here. It doesn't know that it has to multiply "Quantity" with "Price". So, let's say we know that, and we're going to tell it: calculate total sales value by multiplying "Quantity" with "Price"..
Now, let's generate. Now, we get something. Does it get the right formula? Yes, it does. It's using the "SUMX" function. It's going through the sales table and multiplying "Quantity" with "Price". That is what we want. So, we're going to add this in and call this "Total Sales". Okay, so in this example, we had to be a bit more specific about what we're expecting, and it looks like it's formatted it as currency as well. I just want to change the decimal places to be zero,.
And let's add this to our table. So, notice, this part keeps updating. It's a bit annoying. I'm just going to exit out. Okay, so this part is nicely done. Now, let's move on to a calculation where we actually need to use the existing measure, something similar to the example we see here. Let's calculate total online sales. So, whether a sale occurred online or in-store is defined here with the type name, whether it was an online purchase or not. So,.
Posts Related:
- Noob vs Pro Excel LOOKUP Values with two spreadsheets
- 6 steps Bullets and Numbering **FREE DOWNLOAD** Infographics in Powerpoint
- Branching / logic in Microsoft Forms Office
I'm just going to try with "Total Online Sales", and let's see if it understands what we want. - ID Card Make
Yeah, no, it's just referencing the measure. That's not what we want. So, I'm going to keep the measure name together: "Total Sales for Online Purchase". Yeah, that's what we want. Let's click on "Generate", and we can see a preview here, but it appears to be cut off. So, the starting point is right. We want to reference the "Total Sales" measure, although notice that.It's referencing the table as well. So, that goes against best practices because measures are empty shells. They don't belong to specific tables. We only need a table name when we're referencing a column of that table. Then, it goes on to do the check whether this equals "Online". But I can't see the rest. There's no "Show More" button here. So I'm just going to add this to the model and let's see what we get. Okay, it's added it. I can see it here. Let's expand. The formula does.
Look complete, but we don't have that nice formatting we had before, so I don't know what's happening here. I'm just going to rename this to "Total Online Sales". Okay, let's commit this. And I'm going to add this to our table. I'm just going to remove this; it's a bit distracting. Let's add it to our table, and we have this right here. Now, the more you use this, you're going to notice that the measure suggestions are very obsessed with "KEEPFILTERS". So, whenever you're.
Doing checks like this, it's going to put it inside the "KEEPFILTERS" function. It does that as a precaution, I guess as a safety measure, because it doesn't know what type of view you're going to be making in your report, and it just wants to be sure that there aren't other filters in your report that might impact this. So, you don't need the "KEEPFILTERS" part all the time. So, if you know what you're doing, feel free to remove it. The part that's really annoying,.
Though, is this part because if you happen to move the Total Sales measure to another table, so let's say you've decided to create a separate measures table, and you want to move all your measures there, this formula that we just created is going to break. So, we can try that. Let's just go to Model View, go to Total Sales, right? So, that's our source measure here, and I'm going to change the table to just something else. In this case,.
Let's put it in "Product". We're going to go back to our report, and this breaks. Why? Because Total Online Sales broke, and the reason it broke is because when it wrote this measure here, it referenced the table which is completely unnecessary. So, we're just going to get rid of that, and everything will work. I'm just going to bring my measure back to where it was. Okay, so just keep this in mind. If you're using this, make sure that you remove the table reference in.
Front of your measure. And obviously, this might be updated as this feature gets better and better. Now, let's do another example, and I want to show you something else you need to watch out for. If I wanted to do total sales of innovative products outside the United States, okay, let's just think this through. Total sales, it should reference my Total Sales measure. Innovative products is something that's defined by product type inside the Product Master.
Data. Outside the United States. Now, I have United States in two different places. One is a part of the Customer Master Data, and another is a part of the Store Master Data. So, whether the store is in the US or the customer is in the US, in this case, I mean the customer should be in the US, but I haven't specified anything. So, let's generate and see what we get. My "Show More" button is back, and we get "Store Country" doesn't equal to "United States". So, it's using the wrong one. So,.
Let's be specific because before we got that pop-up to be specific, it says if we want to use Customer Country, yes, that's the one we want. So, let's generate, and what do we get again? We get "Store Country". Why? Why don't we get "Customer Country"? It sees it here, but it's not able to generate the formula, and the reason is "country" is a part of the hierarchy, and the original field is hidden, right? I'm just following best practices, so let's go to the model.
And see what happens if I unhide "country". I go back to my measure, it's generating,
And now it works. Now it references "customer country", so if your field is a part of a hierarchy and you don't have it outside separately visible, Suggested Measures currently can't see it. You don't have this problem if you are going to write the formula from scratch yourself. One last thing I want to try is this "average daily sales". Let's generate. Okay, so it's not.Able, but as far as I'm concerned, this is natural language. I literally have no idea what you're talking about. Or should I specify "average sales per day"? Just generate. Yes, that's probably what I had to do, but it's also giving me a very long formula. Again, it's using the KEEPFILTERS function. In this specific case, I don't need it. Then it's referencing the measure, which is fine, but again using the table name, and it's putting it inside the CALCULATE function,.
Which I definitely don't need. So, we're going to add this in, but we have to make a lot of updates to make this look like it was written by someone who knows what they're doing. I'm going to remove the KEEPFILTERS part, and we don't need the CALCULATE here. We don't need this, so that's all we need. Let's just get rid of this because it tries to generate on the fly as well. Okay, so that's our measure..
Let's see where it ended up, "average daily sales". I'm just going to add this to the table. Something you need to watch out for, though, is that sometimes it suggests measures that are also completely wrong. So before I started filming this, I was just trying out "average daily sales", and it gave me a formula that was averaging my calendar dates. Those numbers happen to be a bit similar to my "average daily sales". So, if I don't pay close attention, or if I don't know.
What I'm doing, and I add such a measure, it can be completely wrong calculations. So, my conclusion is this has a long way to go, and yeah, we still need to learn DAX. So, I personally would stay away from this, and the only two cases I can see myself using this is one, I'm procrastinating on writing a function because it's kind of too complex, and I just get started here, and then obviously tweak it to get it to work, and number two,.
If I forget some specific syntax. So, for example, for the "OR" condition, if I forget how that's done, I could ask it here and get started faster with my analysis. Now, I'm not sure if you had a chance to test this for yourself. If you did, let me know what you thought about this in the comments below. As always, thank you for being here, thank you for watching, and I'm going to catch you in.
