One way Power BI makes an admin’s or data analyst’s life easier is by providing an alternative to having to deal with DAX expressions. We’ll demonstrate by creating a basic measure based on conditions from another column. Let’s use the example of a new sales pipeline report using Microsoft Dynamics 365 with Power BI.
Let’s say I want an Opportunity Pipeline report and dashboard with only high probability deals, grouped by close week — not close day. I’ve already gone through the exercise of creating my week column (see this blog post) and now I want to create a column that will return Estimated Revenue only if the rating is “Hot”. In this case, we will create a column that will provide a sum of Estimated Revenue based on our criteria.
In my Power BI query, I am going to add a conditional column.
For Dynamics 365 admins this will feel familiar to an Entity Business Rule. Name your column, then enter the conditions. Below we’re defining the condition Opportunities rated “Hot”, and return that row’s Estimated Revenue.
Our values are returned appropriately in this new column.
Power BI also provides the formula for this function, which is helpful for context as you’re learning more about Power BI and the DAX functions and measures available. Be sure to change the type to a number if appropriate, then close and apply these changes, and the new columns will be available to build visualizations from. Look at the example below including Hot Opportunities by Owner, Estimated Revenue, and Estimated Close Week.
Once again, Power BI to the rescue! Need help transforming or getting more out of your Dynamics 365 data? Contact or chat with Dyn365Pros.
By Erin Karatkiewicz, Dynamics 365 Application Consultant, Dyn365Pros, Microsoft Dynamics 365 Partner, San Diego, Southern California.
