Kategorie: Data Management

Power BI Tricks: 20 Unverzichtbare DAX-Tricks für deine Power BI Berichte

Bücher von Alexander Loth: "Datenvisualisierung mit Power BI" und "Teach Yourself Visually Power BI"
Noch mehr Power BI Tricks in diesen Büchern: „Datenvisualisierung mit Power BI“ und „Teach Yourself Visually Power BI“

Heute möchte ich mit euch einige meiner Lieblings-DAX-Tricks für Power BI teilen. Diese Power BI Tricks werden euch dabei helfen, eure Berichte — sei es in Power BI Desktop oder in Microsoft Fabric — auf das nächste Level zu heben!

20 ultimative DAX-Tricks — einfach erklärt

  1. CALCULATE für Kontextänderungen 🛠️
    CALCULATE ist eine super Funktion, die den Kontext ändert, in dem Daten analysiert werden.
    Zum Beispiel: CALCULATE(SUM('Umsatz'[Umsatzbetrag]), 'Umsatz'[Region] = "West") berechnet die Summe der Verkäufe in der Westregion. Ziemlich cool, oder?
  2. RELATED für den Zugriff auf Daten aus verwandten Tabellen 🔄
    Mit RELATED könnt ihr auf Daten aus einer Tabelle zugreifen, die mit der aktuellen Tabelle verwandt ist.
    Zum Beispiel: RELATED('Product'[Product Name]) holt den Produktnamen, der zur aktuellen Zeile gehört. Einfach und effektiv!
  3. EARLIER für Zeilenkontext 🕰️
    EARLIER ist super nützlich, wenn ihr auf Daten aus einem früheren Zeilenkontext zugreifen wollt.
    Zum Beispiel: CALCULATE(SUM('Sales'[Sales Amount]), FILTER('Sales', 'Sales'[Sales ID] = EARLIER('Sales'[Sales ID]))) – ein bisschen kompliziert, aber unglaublich mächtig!
  4. RANKX für Ranking 🏅
    Mit RANKX könnt ihr Werte in einer Spalte ranken.
    Zum Beispiel: RANKX(ALL('Sales'), 'Sales'[Sales Amount], , DESC) rankt Verkaufsbeträge in absteigender Reihenfolge. Wer ist der Beste? Jetzt könnt ihr es herausfinden!
  5. DIVIDE für sichere Division 🧮
    DIVIDE führt eine Division durch und behandelt Divisionen durch Null.
    Zum Beispiel: DIVIDE([Total Sales], [Total Units]) teilt den Gesamtverkauf durch die Gesamteinheiten und gibt BLANK() für Divisionen durch Null zurück. Keine Fehlermeldungen mehr wegen Division durch Null!
  6. SWITCH für mehrere Bedingungen 🔄
    SWITCH ist eine bessere Alternative zu verschachtelten IFs.
    Zum Beispiel: SWITCH([Rating], 1, "Schlecht", 2, "Durchschnittlich", 3, "Gut", "Unbekannt") weist ein Label basierend auf der Bewertung zu. Schluss mit endlosen IFs!
  7. ALL zum Entfernen von Filtern 🚫
    ALL entfernt Filter von einer Spalte oder Tabelle.
    Zum Beispiel: CALCULATE(SUM('Sales'[Sales Amount]), ALL('Sales')) berechnet den Gesamtverkauf, ohne Rücksicht auf irgendwelche Filter. Freiheit für eure Daten!
  8. CONCATENATEX für String-Aggregation 🧵
    CONCATENATEX verknüpft eine Spalte von Strings.
    Zum Beispiel: CONCATENATEX('Sales', 'Sales'[Product], ", ") verknüpft Produktnamen mit einem Komma als Trennzeichen. Wer braucht schon eine Liste, wenn man alles in einem String haben kann?
  9. USERELATIONSHIP für inaktive Beziehungen 🔄
    USERELATIONSHIP ermöglicht es euch, inaktive Beziehungen zu nutzen.
    Zum Beispiel: CALCULATE(SUM('Sales'[Sales Amount]), USERELATIONSHIP('Sales'[Date], 'Calendar'[Date])) berechnet den Verkauf unter Verwendung einer inaktiven Beziehung. Keine Beziehung ist jemals wirklich inaktiv!
  10. SAMEPERIODLASTYEAR für Vergleiche mit dem Vorjahr 📆
    SAMEPERIODLASTYEAR berechnet den gleichen Zeitraum im Vorjahr.
    Zum Beispiel: CALCULATE(SUM('Sales'[Sales Amount]), SAMEPERIODLASTYEAR('Calendar'[Date])) berechnet den Verkauf für den gleichen Zeitraum im letzten Jahr. Perfekt für Jahresvergleiche!
  11. BLANK für fehlende Daten 🕳️
    BLANK gibt ein Leerfeld zurück.
    Zum Beispiel: IF('Sales'[Sales Amount] = 0, BLANK(), 'Sales'[Sales Amount]) gibt ein Leerfeld zurück, wenn der Verkaufsbetrag null ist. Keine Daten? Kein Problem!
  12. FORMAT für benutzerdefinierte Formatierung 🎨
    FORMAT formatiert einen Wert basierend auf einer benutzerdefinierten Formatzeichenfolge.
    Zum Beispiel: FORMAT('Sales'[Sales Date], "MMM-YYYY") formatiert das Verkaufsdatum als „MMM-YYYY“. Schön und ordentlich!
  13. HASONEVALUE für die Validierung von Einzelwerten 🎯
    HASONEVALUE überprüft, ob eine Spalte nur einen eindeutigen Wert hat.
    Zum Beispiel: IF(HASONEVALUE('Sales'[Region]), VALUES('Sales'[Region]), "Mehrere Regionen") überprüft, ob es nur eine Region gibt. Eindeutigkeit ist wichtig!
  14. ISFILTERED zur Filtererkennung 🕵️‍♀️
    ISFILTERED überprüft, ob eine Spalte gefiltert ist.
    Zum Beispiel: IF(ISFILTERED('Sales'[Region]), "Gefiltert", "Nicht gefiltert") überprüft, ob die Spalte Region gefiltert ist. Wer hat hier gefiltert?
  15. MAXX für maximale Werte in einer Tabelle 📈
    MAXX gibt den maximalen Wert in einer Tabelle zurück.
    Zum Beispiel: MAXX('Sales', 'Sales'[Sales Amount]) gibt den maximalen Verkaufsbetrag zurück. Wer ist der Größte?
  16. MINX für minimale Werte in einer Tabelle 📉
    MINX gibt den minimalen Wert in einer Tabelle zurück.
    Zum Beispiel: MINX('Sales', 'Sales'[Sales Amount]) gibt den minimalen Verkaufsbetrag zurück. Wer ist der Kleinste?
  17. COUNTROWS zum Zählen von Zeilen in einer Tabelle 🧮
    COUNTROWS zählt die Anzahl der Zeilen in einer Tabelle.
    Zum Beispiel: COUNTROWS('Sales') zählt die Anzahl der Zeilen in der Verkaufstabelle. Wie viele sind es?
  18. DISTINCTCOUNT zum Zählen eindeutiger Werte 🎲
    DISTINCTCOUNT zählt die Anzahl der eindeutigen Werte in einer Spalte.
    Zum Beispiel: DISTINCTCOUNT('Sales'[Product]) zählt die Anzahl der eindeutigen Produkte. Wie viele verschiedene Produkte haben wir?
  19. CONTAINS für Lookup-Szenarien 🔍
    CONTAINS überprüft, ob eine Tabelle eine Zeile mit bestimmten Werten enthält.
    Zum Beispiel: CONTAINS('Sales', 'Sales'[Product], "Produkt A") überprüft, ob „Produkt A“ in der Verkaufstabelle existiert. Ist es drin oder nicht?
  20. GENERATESERIES zur Erzeugung einer Zahlenreihe 📊
    GENERATESERIES erzeugt eine Zahlenreihe.
    Zum Beispiel: GENERATESERIES(1, 10, 1) erzeugt eine Zahlenreihe von 1 bis 10 mit einem Schritt von 1. Zählen war noch nie so einfach!

Noch mehr Power BI Tricks

📚 Wenn du noch tiefer in die Welt von Power BI eintauchen möchtest, schau dir doch mal meine Power BI Bücher an 🔗 Datenvisualisierung mit Power BI (Amazon) und 🔗 Teach Yourself Visually Power BI (Amazon) – beide sind vollgepackt mit noch mehr Tipps, Tricks und Anleitungen, die dir helfen, das Beste aus deinen Daten herauszuholen.

Hast du Fragen und Anregungen, dann lass es mich gerne wissen. Außerdem freue ich mich, wenn du meine Power BI Tweets teilst:

„Power BI Tricks: 20 Unverzichtbare DAX-Tricks für deine Power BI Berichte“ weiterlesen

What is Microsoft Fabric? Unveiling Microsoft’s New Data Platform for the Era of AI

What is Microsoft Fabric: The Data Platform for the Era of AI
What is Microsoft Fabric: The Data Platform for the Era of AI

In the complex world of data analytics, a data lake serves as a centralized repository where you can store all your structured and unstructured data at any scale. It offers immense flexibility, allowing you to run big data analytics and adapt to the needs of various types of applications. But imagine having more than just a data lake. Imagine having an entire suite of data management and analytics services that work seamlessly together. That’s where Microsoft Fabric comes in.

Microsoft Fabric is an all-in-one analytics solution designed for enterprises. It spans everything from data movement and data science to Real-Time Analytics and business intelligence. It offers a comprehensive suite of services, including a data lake, data engineering, and data integration, all conveniently located in one platform.

Before we dive in, don’t miss out on joining our LinkedIn group, Microsoft Fabric & Power Platform!

Use Cases of Microsoft Fabric in Data-Driven Companies

Microsoft Fabric covers all analytics requirements relevant to a Data-Driven Company. Every user group, from Data Engineers to Data Analysts to Data Scientists, can work with the data in a unified way and easily share the results with others. The areas of application at a glance:

  • Data Engineering: Data injected with the Data Factory can be transformed with high performance on a Spark platform and democratized via the Lakehouse. Models and key figures are created directly in Fabric.
  • Data Warehousing: A powerful, scalable SQL engine enables stable and high-performance workloads for datasets and reports. Data is stored in native formats in the data lake. As a business intelligence platform, Power BI provides fast and intuitive access and cross-database analysis.
  • Self-Service Analytics: Following the data mesh paradigm, a single data team can be provided with a decentralized self-service platform for building and distributing their own data products.
  • Data Science: Azure Machine Learning functionalities are available by default. Machine learning models for applied AI can be trained, deployed, and operationalized in the Fabric environment.
  • Real-Time Analytics: With Real-Time Analytics, Fabric includes an engine optimized for analyzing streaming data from a wide variety of sources – such as apps, IoT devices, or human interaction.
  • Data Governance: The OneLake as a unified repository enables IT teams to centrally manage and monitor governance and security standards for all components of the solution.

Users can also be supported at all levels by AI technologies. With Microsoft Copilot, Microsoft Fabric offers an intelligent chatbot that translates voice instructions into concrete actions. Developers have the opportunity, for example, to create their program codes, set up data pipelines, or build models for machine learning in this way. In the same way, business users can use the copilot to generate their reports and visualizations for data analysis using voice input alone.

Simplifying Data Analytics: How Microsoft Fabric Offers a Unified, End-to-End Solution

With Fabric, you don’t need to piece together different services from multiple vendors. Instead, you can enjoy a highly integrated, end-to-end, and easy-to-use product that is designed to simplify your analytics needs. One conceivable deployment scenario for the future is data mesh domains with Microsoft Fabric that are connected to an existing lakehouse based on Azure Data Lake Storage Gen2 and Databricks or Synapse. In this setup, the lakehouse continues to handle the core data preparation tasks.

Meanwhile, the decentralized domain teams can use the quality-assured Lakehouse data via Microsoft Fabric using shortcuts to create and deploy their own use cases and data products. Such an approach could prove to be an ideal option, as it optimally complements the advantages of both approaches. The platform is built on a foundation of Software as a Service (SaaS), which takes simplicity and integration to a whole new level.

Microsoft Fabric is not just another addition to the crowded data analytics landscape. Centered around Microsoft’s OneLake data lake, it boasts integrations with Amazon S3 and Google Cloud Platform. The platform consolidates data integration tools, a Spark-based data engineering platform, real-time analytics, and, thanks to upgrades in Power BI, visualization, and AI-based analytics into a single, unified experience.

Microsoft Fabric Pricing Streamlines Your Data Stack for Optimal Cost Efficiency

The rapid innovation in data analytics technologies is a double-edged sword. On one hand, businesses have a plethora of tools at their disposal. On the other, the modern data stack has become increasingly fragmented, making it a daunting task to integrate various products and technologies. Microsoft Fabric aims to eliminate this „integration tax“ that companies have grown tired of paying.

Microsoft Fabric is built around a unified compute infrastructure and a single data lake. This uniformity extends to product experience, governance, and even the business model. The platform brings together all data analytics workloads—data integration, engineering, warehousing, data science, real-time analytics, and business intelligence—under one roof.

Microsoft Fabric introduces a simplified pricing model focused on a common Fabric compute unit. This virtualized, serverless computing allows businesses to optimize costs by reusing the capacity they purchase. The multi-cloud approach, with built-in support for Amazon S3 and upcoming support for Google Storage, ensures that businesses are not locked into a single cloud vendor.

Enhanced Data Governance with Microsoft Purview

Data governance is another area where Microsoft Fabric excels. Using Microsoft Purview, allows businesses to manage data access meticulously. For instance, confidential data exported to Power BI or Excel will automatically inherit the same confidentiality labels and encryption rules, ensuring security.

Microsoft Fabric also offers a no-code developer experience, enabling real-time data monitoring and action triggering. The platform will soon incorporate AI Copilot, designed to assist users in building data pipelines, generating code, and constructing machine learning models.

My Personal Experience so far

Having personally demoed Fabric to over 20 enterprises, the excitement is palpable. The platform simplifies data infrastructure while offering the flexibility of a multi-cloud approach. Most notably, it’s built around the open-source Apache Parquet format, allowing for easier data storage and retrieval.

Microsoft Fabric is currently in public preview and will be enabled for all Power BI tenants starting July 1. The platform promises to be more than just a tool; it aims to be a community where data professionals can collaborate, share knowledge, and grow. So, when someone asks you, „What is Microsoft Fabric?“ you’ll know it’s not just a product; it’s a revolution in data analytics.

Join our Microsoft Fabric & Power Platform LinkedIn Group!

Our LinkedIn group has changed its name to Microsoft Fabric & Power Platform to reflect the evolving ecosystem and the seamless integration between Power Platform technologies like Power BI, Power Apps, and Power Automate with Microsoft Fabric tools like OneLake and Synapse.

If you’re as excited as I am about the future of data analytics and business intelligence, then I’ll invite you to join our LinkedIn group, Microsoft Fabric & Power Platform, a community dedicated to professionals who are eager to stay ahead of industry trends.

Power BI Book: Teach Yourself VISUALLY Power BI Hits #1 Amazon’s Hot New Release in Information Management

Power BI book: Teach Yourself VISUALLY Power BI is number 1 New Release in Information Management
Power BI book: Teach Yourself VISUALLY Power BI is an Amazon Hot New Release

I’m thrilled to announce that my latest book, Teach Yourself VISUALLY Power BI, has achieved the #1 spot in Amazon Hot New Releases in Information Management! This is an exciting milestone, and it’s all thanks to you!

Your ideal guide for Power BI

If you’re new to the world of data visualization and want to get started with Power BI — a tool widely adopted by businesses for its ease of use and powerful capabilities — this book is your ideal guide. It’s a comprehensive, fully visual guide to Microsoft Power BI, and it’s designed to make data visualization as accessible and fun as possible.

This Power BI book brings together all the resources you need to master the everyday use of this powerful data visualization software. Whether you’re looking to understand the basic functions of Power BI or dive into its more advanced features, this book has you covered.

Here’s what you’ll find inside:

  • Step-by-step instructions for working with, transforming, and processing data sources.
  • Tips for customizing data visualizations to create informative and presentation-ready charts and graphs.
  • Full-color, two-page tutorials on the more advanced features of Power BI.

I want to take a moment to extend a huge thank you to all of you. Your ideas, your feedback, your support – these are what truly make Teach Yourself VISUALLY Power BI something special. You’ve helped me realize this not just this project, but all my previous book projects as well, and I’m so grateful.

Get your copy today

Teach Yourself VISUALLY Power BI is available now at your local bookstore and on Amazon. Don’t miss out on the opportunity to enhance your data visualization skills and gain a new perspective on how to communicate information effectively.

Already got your copy of Teach Yourself VISUALLY Power BI? Feel free to drop me a comment on how you like it:

„Power BI Book: Teach Yourself VISUALLY Power BI Hits #1 Amazon’s Hot New Release in Information Management“ weiterlesen

DataMustRead Book Club Pick: Discover the Art of Data Visualization with Show Me the Numbers

Discover the Art of Data Visualization with Show Me the Numbers: Designing Tables and Graphs to Enlighten
Discover the Art of Data Visualization with Show Me the Numbers: Designing Tables and Graphs to Enlighten

This month’s #DataMustRead book club selection is Show Me the Numbers (Amazon) by Stephen Few. In my ongoing mission to explore and share the most insightful resources in the field of data, I’ve chosen this exceptional guide on how to effectively present quantitative information.

Data Visualization with Show Me the Numbers

Data visualization is an essential skill in today’s data-driven world. It’s about translating complex data into an easy-to-understand visual format that helps us make sense of the numbers and tell compelling stories. It’s an area I’ve explored in my own recent book, Teach Yourself VISUALLY Power BI (Amazon).

Show Me the Numbers is a master class in the principles and practices of data visualization. It’s an ideal guide for anyone involved in communicating with numbers – whether you’re a business professional, analyst, researcher, or student. And if you’re a fan of Teach Yourself VISUALLY Power BI, you’ll find Show Me the Numbers a complementary read.

Gleaning Insights from Data

The book starts with the basics, explaining the types of data and which tables or graphs are best for communicating them. But it doesn’t stop there. It builds on these insights by delving into more creative elements of data visualization. The beauty of this book is its broad appeal – it’s as accessible to beginners as it is enlightening to seasoned data professionals.

Stephen Few is a renowned expert in the field of data visualization, and his expertise shines through in this book. He shows us how to choose the right chart type, how to use color and text effectively, and how to avoid common pitfalls. Throughout the book, he supports his teachings with relevant examples and exercises that allow readers to apply the concepts to their own data.

Show Me the Numbers: A Practical Approach to Data Visualization

Show Me the Numbers is refreshingly practical. It offers sensible advice that you can apply directly to your work using modern tools and practices. Unlike some books that present unusual and pretty charts for unusual cases, Show Me the Numbers focuses on functional, effective visualization techniques that can be used in real-world scenarios.

One of the most appreciated aspects of this book, as mentioned in several reviews, is its readability. Even the statistical explanations are simple, yet accurate. It’s a technical book that you can comfortably read in your backyard and relax with while learning valuable skills.

Join the #DataMustRead Book Club

In conclusion, Show Me the Numbers is a highly recommended read for anyone interested in data visualization. It’s a book that takes the wisdom of classic authors in the field and builds on it with practical, up-to-date advice.

Ready to dive in? Order your copy of Show Me the Numbers and support both me and the author. Join the #DataMustRead book club, share your thoughts, and let’s continue our journey to master the world of data together!

Follow me on Twitter and LinkedIn for more insights into the world of data visualization. As always, happy reading, and feel free to share your thoughts in the comments section of this LinkedIn post or with a reply on this Twitter thread.

„DataMustRead Book Club Pick: Discover the Art of Data Visualization with Show Me the Numbers“ weiterlesen

Master Microsoft Power BI with My New Book: Teach Yourself VISUALLY Power BI

Alexander Loth holding his books Teach Yourself VISUALLY Power BI, Decisively Digital and Visual Analytics with Tableau
Alexander Loth holding his books Teach Yourself VISUALLY Power BI, Decisively Digital and Visual Analytics with Tableau

A Comprehensive, Fully Visual Guide to Data Visualization and Analytics

I am thrilled to announce the release of my latest book, Teach Yourself VISUALLY Power BI – a comprehensive and fully visual guide to mastering Microsoft Power BI. This book is designed to help both beginners and experienced users improve their skills in data visualization and analytics using Power BI, a powerful tool that has become an industry standard. In this blog post, I’ll give you a sneak peek into the contents of the book and how it can help you become a Power BI pro.

Why Teach Yourself VISUALLY Power BI?

Power BI is a robust data visualization software that enables users to transform raw data into meaningful insights through stunning visuals. However, learning Power BI can be challenging, especially for those who are new to the world of data visualization. That’s where Teach Yourself VISUALLY Power BI comes in. The book adopts a visual learning approach, combining step-by-step instructions with clear screenshots to walk readers through the basic and advanced functions of Power BI.

What’s Inside Teach Yourself VISUALLY Power BI?

This guide offers a comprehensive learning experience, covering a wide range of topics, including:

  1. Working with, transforming, and processing data sources: Learn how to connect to various data sources, clean and transform data, and create relationships between tables.
  2. Customizing data visualizations: Discover how to create informative and presentation-ready charts and graphs by customizing their appearance, colors, and interactivity.
  3. Advanced Power BI features: Explore app integrations, data access with DAX, and other advanced features that will help you get the most out of Power BI.
  4. Real-world examples and guidance: Apply your learnings to real-world scenarios with practical examples and expert tips throughout the book.

Who Can Benefit from Teach Yourself VISUALLY Power BI?

This book is suitable for a wide range of readers, including:

  1. Beginners: If you’re new to Power BI or data visualization, Teach Yourself VISUALLY Power BI provides an accessible introduction to help you get started.
  2. Intermediate users: For those with some experience in Power BI, the book offers valuable tips and advanced techniques to enhance your skills further.
  3. Professionals: Data analysts, business intelligence professionals, and managers can use this book to improve their data visualization and reporting capabilities using Power BI.

Get your copy today!

Teach Yourself VISUALLY Power BI is a must-have resource for anyone looking to master Microsoft Power BI. Whether you’re a beginner or an experienced user, this book offers valuable insights and guidance to help you harness the full potential of this powerful data visualization tool.

Don’t miss out – grab your copy today and embark on your journey toward becoming a Power BI pro! Also, follow me on Twitter and LinkedIn for more insights.