Tag Archives: Dynamic

Dynamic relationships based on the selection

Got an interesting question recently where someone wanted to be able to switch the relationships automatically based on which slicer was selected.

Take the following example: Dynamic relationships based on the selection

Here we have the same customer slicer shown twice, in this case we want to slice by customer but if the user uses the ‘baseline customer’ slicer the data should only filter on the ‘Baseline customer’ table and ignore the ‘Real customer’ slicer regardless if it is set.

First thing I do is copy the Customer table to be able to add the same value twice in a slicer using a calculated table:

Baselineregion = DimSalesTerritory

Next I add relationships for both. This is optional for baselineregion, it depends on what else you want to do with it. This solution will ignore it anyway but other measures will be affected.

Now to make things a bit more complicated we add another baseline dimension for regions using the same calc table technique: Dynamic relationships based on the selection

The model now looks like this:

 Dynamic relationships based on the selection

Now for the final step some real DAX magic using my new favorite function TREATAS, inspired by Srini one of main DAX developers on Power BI and major DAX overlord. Ready?

This measure will detect which relationship is used and set the according filter.

SalesAmount 2 =
VAR CustomerCodes =
        UNION (
            FILTER (
                KEEPFILTERS ( VALUES ( ‘Baseline customer'[CustomerKey] ) ),
                ISCROSSFILTERED ( ‘Baseline Customer’ )
            FILTER (
                KEEPFILTERS ( VALUES ( ‘DimCustomer'[CustomerKey] ) ),
                NOT ISCROSSFILTERED ( ‘Baseline customer’ )
VAR TerritoryCodes =
        UNION (
            FILTER (
                KEEPFILTERS ( VALUES ( Baselineregion[SalesTerritoryKey] ) ),
                ISCROSSFILTERED ( ‘Baselineregion’ )
            FILTER (
                KEEPFILTERS ( VALUES ( ‘DimSalesTerritory'[SalesTerritoryKey] ) ),
                NOT ISCROSSFILTERED ( ‘Baselineregion’ )
        SUM ( FactInternetSales[SalesAmount] ),
        ALL ( DimCustomer ),
        ALL ( DimSalesTerritory )

The trick here is 3 things:

  1. We are using TREATAS to join the fact table with the correct dimension table, the results of this join are stored in a variable as a table.
  2. We use UNION and ISCROSFILTERED to determine which table to use in the join. You can think of this as an workaround for table expressions not supporting IF. When the ‘baseline customer’ table is filtered it returns a table of selected CustomerKeys from the ‘baseline customer’  table, each row is are FILTERed by “TRUE” (being the result of ISCROSSFILTERED). All rows of regular customer table will filtered out by the “FALSE” (again being the result of NOT CROSSFILTERED). When the Baseline customer is not filtered the reverse will happen. A genius solution if you ask me  Dynamic relationships based on the selection (again full credits go to Srini).
  3. Finally in returning the results, we use CALCULATE to change the filter context. By using ALL we overwrite any filters set directly on the dimension table and propagate our own filters that is getting returned from the variables.

Now we get the results we want, you can see below the new “SalesAmount 2” measure now is getting filtered by the baseline tables. The regular measure is not working as it is filtered by both dimensions:

 Dynamic relationships based on the selection

That’s it. One thing to remember of course is that performance will not be as optimal as using real relationships, Marco and Alberto described this in a great blog post here. You can download the workbook here.

I am using DAX Formatter to show the DAX above:
 Dynamic relationships based on the selection

Let’s block ads! (Why?)

Kasper On BI

A 180-Year-Old Business and a Startup Both Manage Growth, Change in Dynamic Health and Beauty Industry

Posted by Branden Jenkins, GM of Retail, Oracle NetSuite

Bigelow A 180 Year Old Business and a Startup Both Manage Growth, Change in Dynamic Health and Beauty IndustryIn New York City’s bustling Greenwich Village, a vintage neon sign from the 1930s invites patrons to explore the vast array of mainstream and offbeat health and beauty products within C.O. Bigelow, America’s oldest apothecary. On the shelves are typical soaps, shampoos, toothpastes and the like, along with Bigelow-branded products like Lemon Body Cream, its recipe unchanged since 1870.

While the wooden shelving, brass finishes and gas chandeliers more than a century old evoke a sense of history, making it, “by far the coolest place in New York to buy anything beauty-related,” as a Stylecaster reviewer put it, C.O. Bigelow still must deal with the challenges of a modern health and beauty company.

For many, that means a carefully cultivated and evocative brand image, showcased on a sleek, image-rich ecommerce website. Key strategies to elevate a company’s profile in this highly competitive market can include celebrity endorsements, an emphasis on organic ingredients, and aggressive social media marketing.

But beauty is only skin deep. Beneath the surface, health and beauty companies need superior operational efficiency to minimize costs and delays. Inventory, ordering and customer service need to function flawlessly to meet and exceed customer expectations.

Bigelow, in its own description, “transports customers back to a time and place of personalized attention, customized formulas and healing, and therapeutic preparations.” The store was founded in 1838, moving in 1902 two doors down the street to its present location. Bigelow has stayed true to those roots throughout its 180-year history, while simultaneously embracing modern technology to succeed in today’s ever-changing market, even in the face of stiff competition and an influx of chain drug stores.

It struck up partnerships with retailers such as Nordstrom, Sephora and Barney’s, growing the wholesale channel 20 to 30 percent annually in recent years. And notably, it does brisk business with global hotels and airlines that supply Bigelow goods to travelers around the world.

Owner Ian Ginsberg, whose grandfather purchased the business in 1939, says technology is a key ingredient to Bigelow’s success in a changing world: “We’re the ones who survived the disruption. We’re a traditional business surviving in a modern world.”

“The game has changed in all facets of retail,” Ginsberg said. “As growth has slowed, you have to learn how to make money on your bottom line. The more tools and technologies you have to improve your bottom line, the better off you are.”

Since 2013, Bigelow has used NetSuite for core functions of financials, reporting, order management, and inventory management across 30,000 SKUs. Especially valuable, Ginsberg said, are dashboards and real-time reporting that help Bigelow improve cost-efficiency and its bottom line.

Ovation Hair, a manufacturer of high-end hair care products in Carlsbad, southern California, doesn’t have the history that Bigelow does, but it’s confronting many of the same challenges. The 50-person company celebrated its 10-year anniversary in 2017 and has seen rapid growth by every measure.

It joins other upstarts that are challenging household names like Estée Lauder, L’Oréal and Unilever by opening new direct-to-consumer channels to complement traditional distribution strategies.

It comes at a special time. Global health and beauty product sales are expected to grow 73 percent between 2016 and 2025, to $ 750 billion, says Inkwood Research, a Boston-based research firm. Manufacturers and retailers need speed and agility to take advantage of growing opportunities.

For Ovation Hairrevenue is growing annually at a double-digit pace. It has expanded its product lineup beyond its flagship Cell Therapy product, which uses a proprietary blend of proteins, vitamins, botanical extracts and amino acids to help both women and men grow thicker, stronger, longer hair. Today, customers can choose from more than 25 products, including shampoos, conditioners, shaving lotions, vitamins and accessories.

Starting out an ecommerce pure play, Ovation Hair products are now sold on the QVC shopping channel, third-party marketplaces and through roughly 75 select salons in the U.S. And the company is growing globally. Earlier in 2017, it introduced international shipping to countries in Latin America, Asia, Europe and Africa, with ordering available online or over the phone. 

Effective marketing has been instrumental in Ovation Hair’s success. Once advertising mostly through radio spots in southern California, the company now reaches customers and cultivates evangelists through YouTube, Facebook, a blog and other social channels. And it’s grown a database of 500,000 contacts for personalized marketing engagement.

On its www.ovationhair.com website, Ovation Hair offers regular promotions, refer-a-friend rewards, a loyalty program and a 100 percent satisfaction guarantee. Subscription replenishment has proven effective in driving recurring revenue. Beyond marketing, Ovation Hair is also an in-house manufacturer, producing its products at a plant in southern California.

“Developing customer intimacy is extremely important for us,” said Erika Sherwood, Ovation Hair Marketing Manager. “The customer dictates what our next steps are in terms of product development and what they’re looking for from the company.”

Over its 10 years, Ovation Hair has capitalized on new marketing and fulfillment channels in the face of increasing competition. “The industry has evolved a lot over the past couple of years, especially with social media and influencers,” Sherwood said. “There are a lot of ‘me-too’ products out there making the same claims without substantiation.”

Deployed in late 2010, NetSuite has been pivotal to Ovation Hair’s success. The company uses NetSuite for financials, inventory, order management and manufacturing, with SuiteCommerce providing a mobile-friendly ecommerce site. NetSuite is also the repository for all customer information, providing a foundation for personalized marketing and customer service.

The ever-evolving health and beauty industry requires retailers to be nimble, agile and adaptable to change. C.O. Bigelow and Ovation Hair, two companies with completely distinct histories and backgrounds, serve as prime examples of retailers that have found success by applying these requirements. A combination of creativity, product innovation, new distribution channels and technology have helped them maintain a competitive edge and thrive. Learn more about how NetSuite supports health and beauty companies, including COOLA, Anisa International, Epicurean, Perfectly Posh and The Beauty Collective, by visiting our health and beauty page.

Learn More at NRF – Retail’s BIG Show

If you are heading to NRF taking place January 14-16, be sure to schedule a demo to experience NetSuite’s software used by health and beauty companies like C.O. Bigelow and Ovation Hair. Your brief, 15-minute demo will have a big impact. NetSuite, through RetailROI, will be donating $ 300 to the Miracle Foundation for each person that schedules and attends a 15-minute demo during NRF, providing clean water to orphans in need.

Let’s block ads! (Why?)

The NetSuite Blog

Dynamic value within Table not being evaluated

 Dynamic value within Table not being evaluated

This is a bit of a follow-up question to my post here. I’m now attempting to replicate the same general code using Connect Four (7×6 grid instead of 3×3).

However, when I pass a Dynamic value to my createBox function, it seems to use values such as board$ 3379[[1,6]] instead of using the actual board variable. I believe this relates to the RuleDelayed issue described here but using With doesn’t seem to help.

When I remove the Dynamic function before board[[x,y]] it works as intended.

createBox[elem_] := Module[{},
    {{White, Rectangle[]}
     , Switch[elem
      , "A", {Red, Disk[{0.5, 0.5}, 0.4]}
      , "B", {Blue, Disk[{0.5, 0.5}, 0.4]}
      , " ", {Thick, Circle[{0.5, 0.5}, 0.4]}
      , _, {}
     }, ImageSize -> 50, Frame -> True, FrameStyle -> Thickness[.02], 
    FrameTicks -> None

   {board = ConstantArray[" ", {7, 6}], player = "A"},
     With[{y = y, x = x}
      , EventHandler[
       createBox[Dynamic@board[[x, y]]]
       , {"MouseClicked" :> (
          If[board[[y, x]] === " ",
            board[[x, y]] = "A";
     , {y, Length@board[[1]], 1, -1}, {x, Length@board}
     ], Spacings -> {0, 0}
   ], WindowTitle -> "Connect Four", WindowSize -> All

Is there a better way to structure this?

Let’s block ads! (Why?)

Recent Questions – Mathematica Stack Exchange

How to Insert Dynamic Tables into Workflow Created Emails in Dynamics 365

Recently for a project I was a part of, one of the requirements given was to insert a table with dynamic values into an email in Dynamics 365. The email this table was being inserted into was being created within an existing workflow using the “Create Record” step for the ‘Email’ entity. The screenshot below shows an example workflow I created with the same email creation step inserted in a workflow created for Accounts and an extra step for adding the table.

image thumb How to Insert Dynamic Tables into Workflow Created Emails in Dynamics 365

To meet this requirement, I decided to use a Custom Workflow Activity (CWA) just after the email was created and just before sending it. In this example, I will use the same methods but I will not be sending out the final email from Dynamics 365.

This CWA would take both the newly created email and the record the workflow is running on, in this case account as inputs. The CWA would first retrieve the data to be shown in a table, which in this example was a list of Contacts related to the Account. Note that the “sdk” variable is of type IOrganizationService.

image thumb 1 How to Insert Dynamic Tables into Workflow Created Emails in Dynamics 365

The  next step would be to format the retrieved values and place them nicely in a table. The code in the screenshot below shows how I created and populated the table but this step could be done in a number of different ways.

image thumb 2 How to Insert Dynamic Tables into Workflow Created Emails in Dynamics 365

Then finally the CWA would find the placeholder within the emails description, which in this case was “

”, replace it with the created table and update the email.

image thumb 3 How to Insert Dynamic Tables into Workflow Created Emails in Dynamics 365

The screenshot below shows the finished email sent out with the dynamically populated table values in Dynamics 365.

image thumb 4 How to Insert Dynamic Tables into Workflow Created Emails in Dynamics 365

Let’s block ads! (Why?)

Magnetism Solutions Dynamics CRM Blog

How to initialize PopupMenu with first element on each dynamic update?

I wrote a function, which lists directories, which may contain “model” files in the following format

{dirpath1->dirname1, dirpath2->dirname2, ...

i.e. in format, suitable for PopupMenu function.

Then I wrote a function, which lists model files in given directory in the same way

{filepath1->filename1, filepath2->filename2, …

I was wishing to select directory in first popup and then select file in second popup and wrote

PopupMenu[Dynamic[dir], GetModelDirectories[]]

Dynamic[PopupMenu[Dynamic[fil], GetMatFiles[dir]]]

It worked, but partially: if I change first popup, second popup turns empty

8zK1G How to initialize PopupMenu with first element on each dynamic update?

and I need explicitly select second popup

Ozr9j How to initialize PopupMenu with first element on each dynamic update?

Is it possible to initialize second popup automatically to the first item in the list?

Let’s block ads! (Why?)

Recent Questions – Mathematica Stack Exchange

Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

Got a good question this week that had me scratching my head for a bit but then I remembered a new function that was added to Power BI (and SSAS) recently called TreatAS. Marco covered it in detail here. So what they wanted to do is have a visual where they can view the sales and compare it with sales of different colors. So let’s get too it.

Too start out please make sure you read Marco’s post really well, the trick I am about to show you works really well but if you can use the alternative (real relationships) it is preferred for performance reasons. Having said that lets continue.

I have a very simple model with Sales by product:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

Now I visualize it by creating a visual that shows Sales by Manufacturer:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

The goal here to show 4 bars:

  • one with the total sales
  • one with sales only for specific colors selected by a slicers
  • one with sales only for specific colors selected by another slicers
  • one with the remaining sales not part the selection

To start I want to populate and create the slicers. I can’t use the values of the table itself as that would filter all the results so I have to create two new tables with these values to make “disconnected” slicers. To do this I create a two new calculated tables

Colors = VALUES(DimProduct[ColorName])


MoreColors = VALUES(Colors[ColorName])

This created two new tables in my model with just the colors:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

Observe I didn’t create any relationships as I want to control this in the measure itself (more on this later)

Next I add the values as slicers to the report:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

So now I want to add the bars for sales of all colors selected in Color 1 and another for all colors in Color 2. To do this I add a new measure using the new TREATAS function:


What this measure does is calculate the Sum of SalesAmount and filtering the ColorName from the DimProduct table with the values if the current selected values of ColorName from the Colors table, like an actual relationship was used.

Now going back to the visual where I added the measure and selected 2 colors, we only see the sales for those 2 colors selected:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

Now adding the same measure for Color 2

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

MoreColorsSelected = CALCULATE(SUM(FactOnlineSales[SalesAmount]),USERELATIONSHIP(MoreColors[ColorName],DimProduct[ColorName]))

As last measure I am adding the remaining sales:

RemainingColors = SUM(FactOnlineSales[SalesAmount])-[ColorsSelected]-[MoreColorsSelected]

Now that I have that I can also add this using stacked charts to create a single bar that is split up dynamically:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

Now this works great but I hope you read Marco’s blog post and read his warning, whenever you can you should always use relationships. While I was working on this blog post it dawned on me that we can just as well use Inactive relationships here. So I went to the diagram view and created them:

 Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

Then instead of using TREATAS I am using our traditional USERELATIONSHIP instead

MoreColorsSelected = //CALCULATE(SUM(FactOnlineSales[SalesAmount]),TREATAS(VALUES(MoreColors[ColorName]),DimProduct[ColorName]))

this will, only for this measure, activate the relationship and filter the product table with the selects colors. This gives the same results but with better performance, now with a small dataset like this you will never notice any issues but if you use this with billions of rows and complex calculations any performance gain will help. It still shows you that you can use TREATAS in other more interesting scenario’s or example lets say we want to see the sum of sales amount for both selections at the same time. I could write something like this:

MoreColorsSelected2 = var Selections = UNION(VALUES(Colors[ColorName]),VALUES(MoreColors[ColorName]))
return CALCULATE(SUM(FactOnlineSales[SalesAmount]),TREATAS(Selections ,DimProduct[ColorName]))

This will use the UNION of these two values as filter for the ColorName column, now we can extend this to do all kind of cool things here but I will leave that up to your imagination  Dynamic data comparisons using disconnected slicers, TreatAs and inactive relationships

You can download the entire file here: https://github.com/Kjonge/DemoWorkbooks/blob/master/selection.pbix

Let’s block ads! (Why?)

Kasper On BI

Dynamic Opportunity for the Dynamics 365/CRM Community at D365UG/CRMUG Summit in Nashville

Summit 2017 CYB CRM7 Border Dynamic Opportunity for the Dynamics 365/CRM Community at D365UG/CRMUG Summit in NashvilleHey y’all!

This fall, step away from your screens, pack up your big ideas, and head to D36UG/CRMUG Summit Nashville, the most comprehensive, impressive, and roarin’ good time conference of the year. As a proud Gold Sponsor, Ledgeview Partners is excited to share this experience with you as we squeeze every last bit of functionality out of the products and get you the ROI you want—and deserve.

Why YOU should attend

D365UG/CRMUG Summit on October 10-13 is THE go-to conference that brings Microsoft Dynamics 365/CRM industry experts, software development vendors, and everyday users together to discuss important issues, trends, product updates, customer pain points, and genuine solutions. The value of this conference is endless!

Here are a few more reasons why you should attend:

  • Geek out over great content: There’s no better instructor than an actual Microsoft Dynamics 365 or Dynamics CRM user. Receive deep level technical training in a focused environment on Dynamics CRM functions and entities that you can utilize every day of the year.
  • Meet & network with your user group buddies: D365UG/CRMUG Summit provides countless opportunities to create lasting relationships by connecting and networking with your Dynamics 365 & CRM peers.
  • Learn from your seriously smart peers: Learn from and connect with Dynamics CRM experts and MVPs on a personal basis.
  • Evaluate and test solutions: Understand third party solutions and learn what they can do for your industry or organization.

>> Save 10% on registration with a code from Ledgeview Partners!

D365UG/CRMUG Summit discounted early bird pricing is available now! Save an additional 10% off your already discounted registration when you use Ledgeview’s exclusive coupon code: PRPLedgeview

So giddy-up, grab your boots, and join Gold Sponsor Ledgeview Partners in Nashville!

D365UG CRMUG Summit Nashville Email Signature Gold 625x105 Dynamic Opportunity for the Dynamics 365/CRM Community at D365UG/CRMUG Summit in Nashville

Ledgeview blk and color dots 300x90 Dynamic Opportunity for the Dynamics 365/CRM Community at D365UG/CRMUG Summit in Nashville

Let’s block ads! (Why?)

CRM Software Blog | Dynamics 365

Explicit dynamic analysis

 Explicit dynamic analysis

I have to simulate a blast on a plate, so I require to input time varying pressure force just like a triangular impulse. Can someone guide me through the process or attach the link from where I could learn it in AceFEM.

Let’s block ads! (Why?)

Recent Questions – Mathematica Stack Exchange

Tech Tip Thursday: Dynamic Power BI reports using Parameters

Microsoft’s Guy in a Cube has been providing tips and tricks for Power BI and Business Intelligence on their YouTube channel since 2014. Occasionally on Thursdays we highlight a different helpful video from the collection.

Did you know that you can dynamically filter data in Power BI using parameters that are stored in an Excel workbook? In this video, Patrick from Guy in a Cube shows us how, using M Functions within Power Query and a gateway to enable data refresh. Check it out!

Let’s block ads! (Why?)

Microsoft Power BI Blog | Microsoft Power BI

DAX “Reanimator” Series, Episode 1: Dynamic TopN Reports via Slicers

Power BI Report thumb DAX “Reanimator” Series, Episode 1: Dynamic TopN Reports via Slicers

Guess how many articles are here on PowerPivotPro.com?  Go ahead and think of a number, I’ll wait.

The answer, at time of writing, is 923.  Rob alone has published 715 articles!  And these date all the way back to 2009.

A lot of these articles are “old,” but folks, the DAX engine is still 99% the same today in Power BI (and Excel 2016) as it was when it first “hit the shelves” in Spring 2010.

The motivation behind this “Reanimator” series, then, is twofold:

  1. Help newer converts/readers rediscover some of the most-awesome techniques previously covered here (without being so lazy as re-posting them in their original form)
  2. “Refresh” those techniques for the brave new world of Power BI (since the vast majority of old articles were written when we only had Power Pivot)

What better way to do that than to re-create those workbooks in Power BI Desktop and embed the report directly…Within. This. Post! wlEmoticon smile DAX “Reanimator” Series, Episode 1: Dynamic TopN Reports via Slicers

A New Age of Self-Service BI Users

I’ve been fortunate enough to be given the honor of sharing with you, our community, all these wonderful posts written by many of our in-house industry experts. Updated in all their glory into the wonderful world of Power BI. Now you can click, slice, interact, touch (…dirty), and drill (dirtier!) with these reports to your hearts desire. Just as the BI gods intended them to be! My hope is that these updates will instill these tools to the growing number self-service BI users just getting into the field and who want to do AWESOME things with their reports.

Highlights From The Original Post(s)

So this update is actually a continuation of not just one…but TWO posts written by Rob in the distance past of 2012 (in technology years that’s basically forever). The two original posts were:

Dynamic TopN Reports Using PowerPivot V2!

Dynamic TopN Reports via Slicers, Part 2

Excel Report thumb DAX “Reanimator” Series, Episode 1: Dynamic TopN Reports via Slicers

Rob demos some pretty ingenious techniques using his (now prolific) disconnected slicers technique to not only control the Top N Number you’d like to see on charts or graphs, but also the Value that you want to see that Top N Number ranked on. I’ve used it in MANY reports I’ve made over the years, always impressing the customers who used them.

Now I don’t want to give too much away in this post, instead directing you back to the walkthrough via the links above. I’m just here to whet your appetite enough with some fancy Power BI Reports, and if you want to learn the DAX code, hop into Rob’s posts.

This “Picture” Below is an Interactive Power BI!

Isn’t Something Missing?

Some of our more avid blog readers may be thinking “wasn’t there a THIRD post about TopN filtering?”. Yes, in fact there was. It was written by guest contributor Colin Banfield and is called Dynamic TopN Reports via Slicers, Part 3. It’s a fantastic post which covers ways to add BottomN metrics, Month/Year slicers, and more. I chose not to use that workbook since I wanted to capture the core story from the original posts written by Rob. If you’re inclined however, I recommend reading all three as they will add real value to your DAX tool belt. Until next time P3 Nation!

Download the Files!

Download the PBIX files


Get Your Files

Let’s block ads! (Why?)