{"id":453,"date":"2014-08-18T13:04:57","date_gmt":"2014-08-18T11:04:57","guid":{"rendered":"http:\/\/www.flip-design.de\/?p=453"},"modified":"2014-08-18T13:04:57","modified_gmt":"2014-08-18T11:04:57","slug":"powerpivot-switch-funktion-oder-auch-wie-komme-ich-ohne-hilfstabellen-bei-uebersetzungen-aus","status":"publish","type":"post","link":"https:\/\/www.flip-design.de\/?p=453","title":{"rendered":"PowerPivot: Switch() Funktion \u2013 oder auch: Wie komme ich ohne Hilfstabellen bei \u00dcbersetzungen aus?"},"content":{"rendered":"

\"PowerPivot_Logo\"<\/a>English version below<\/a><\/p>\n

Wenn man einfache \u00dcbersetzungen in Daten vornehmen wollte, bspw. Gruppenschl\u00fcssel in Namen zu \u00fcbersetzen, bediente ich mich bisher immer an Hilfstabellen die ich in Excel definiert und dann in das Datenmodell eingebunden haben.<\/p>\n

So in etwa \u2013 eine Tabelle beinhaltet einen Schl\u00fcssel um eine Kundengruppe zu beschreiben. Die Beschreibung befindet sich in einer Hilfstabelle die in PowerPivot eingebunden wird und per Verkn\u00fcpfung dann die \u00dcbersetzung vornimmt. Problem dabei ist meist, wenn ein Wert in den Daten vorkommt, der nicht in der \u00dcbersetzung Tabelle vorkommt. Da habe ich dann per einer berechneten Spalte per Bedingter Anweisung die fehlenden Werte markiert.<\/p>\n

Hier der vorherige Weg:<\/p>\n

\"2014-08-18_12-37-08\"<\/a> \"2014-08-18_12-39-03\"<\/a><\/p>\n

 <\/p>\n

 <\/p>\n

  <\/p>\n

  <\/p>\n

 <\/p>\n

  <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

 <\/p>\n

Hier kommt nun die SWITCH Funktion ins Spiel.<\/p>\n

Mit dieser kann ich Werte \u00fcberpr\u00fcfen und \u00fcbersetzen. Grunds\u00e4tzlich geht das Ganze auch mit einer IF Funktion, aber deutlich un\u00fcbersichtlicher und somit schlecht in der Wartung.<\/p>\n

=SWITCH(Customers[Group]; \"A\"; \"High Priority\"; \"B\"; \"B Customers\"; \r\n\"C\"; \"C Customers\"; \"no valid group\")<\/pre>\n
Der Grundsyntax von SWITCH:<\/p>\n
SWITCH(expression<\/em>, \r\n \u00a0\u00a0 value1, result1<\/em>,\r\n \u00a0\u00a0 value2, result2<\/em>,\r\n \u00a0\u00a0\u00a0 :\r\n \u00a0\u00a0\u00a0 :\r\n \u00a0\u00a0\u00a0 else<\/em>\r\n \u00a0\u00a0 )<\/pre>\n
\"2014-08-18_12-41-07\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
<\/a>PowerPivot Switch Function \u2013 or the end of the linked translation tables?<\/h1>\n
If you wanted to make simple translations in data, for example I want to translate a Customer Group Key in a name, I used to create translations Tables in Excel and then integrated into the data model of PowerPivot.
\nSomething like that – a table contains a key to a customer group to describe. The description is another table that is linked in PowerPivot and then performs the translation by a relationship. Problem is mostly, if a value exists in the data that is not present in the translation table. Since then I have checked the missing values \u200b\u200bby a calculated column using a IF\/ELSE statement.
\nHere the previous path:
\n\"2014-08-18_12-37-08\"<\/a> \"2014-08-18_12-39-03\"<\/a><\/p>\n
 <\/p>\n
 <\/p>\n
  <\/p>\n
  <\/p>\n
 <\/p>\n
  <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
 <\/p>\n
Here are the SWITCH function:
\nWith this I can check values \u200b\u200band translate. Basically, the whole thing goes well with an IF function, but much less clear and thus poorly in maintenance.<\/p>\n
 =SWITCH (Customers[Group], \"A\", \"High Priority\", \"B\", \"B Customers\", \r\n\"C\", \"C Customers\", \"no valid group\")<\/pre>\n
The basic syntax of SWITCH:<\/p>\n
SWITCH(expression<\/em>, \r\n \u00a0\u00a0 value1, result1<\/em>,\r\n \u00a0\u00a0 value2, result2<\/em>,\r\n \u00a0\u00a0\u00a0 :\r\n \u00a0\u00a0\u00a0 :\r\n \u00a0\u00a0\u00a0 else<\/em>\r\n \u00a0\u00a0 )<\/pre>\n
\"2014-08-18_12-41-07\"<\/a>
\n <\/p>\n","protected":false},"excerpt":{"rendered":"
English version below Wenn man einfache \u00dcbersetzungen in Daten vornehmen wollte, bspw. Gruppenschl\u00fcssel in Namen zu \u00fcbersetzen, bediente ich mich bisher immer an Hilfstabellen die ich in Excel definiert und dann in das Datenmodell eingebunden haben. So in etwa \u2013 … Continue reading →<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":270,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0},"categories":[30,41,26,36],"tags":[46,44],"_links":{"self":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/453"}],"collection":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=453"}],"version-history":[{"count":5,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/453\/revisions"}],"predecessor-version":[{"id":461,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/posts\/453\/revisions\/461"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=\/wp\/v2\/media\/270"}],"wp:attachment":[{"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=453"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=453"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.flip-design.de\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=453"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}