Skip to main content

Power BI Journey: Blog #3



I am now in the third tutorial on Power BI. In this lesson, the topic is on creating and managing relationships. Important concepts that were introduced are cardinality i.e. many-to-1 (*-1) or (one-to-many (1-*), cross-filter where if we select "Both", this means that we can think of many separate tables as one table.

I have these three separate tables.

Apocalypse Sales:

Apocalypse Store:

Customer_Information:

Notice that each of the values in the Customer ID column of the third table can be seen in the Cust ID column of the first table. But notice that there are only four rows in Customer_Information table while there are many rows in the Apocalypse Sales table. The cardinality of the relationship means having unique or multiple instances per value for the joining field between two tables. This means that the cardinality of the relationship from Apocalypse Sales and Customer_information in terms of Customer ID = Cust ID is Many-to-one. Conversely, the cardinality of the relationship from Customer_information to Apocalypse Sales in terms of Customer ID = Cust ID is one-to-many. If we relate this to SQL, Customer ID is the Primary Key.

Using the same principle, the cardinality of the relationship between Apocalypse Store and Apocalypse Sales in terms of Product_ID is one-to-many. Conversely, the cardinality of the relationship between Apocalypse Sales and Apocalypse Store in terms of Product_ID is many-to-one.

This is the reason why we have the following model.



As an output, I was able to create a table that list the different states associated with each customer and then determine the number of product ids each customer purchased.

In other words, in the Apocalypse Store, we have 10 products being sold and the customer from Minnesota state has purchased 7 out of the 10 products.


Labels:

Comments

Post a Comment

Popular posts from this blog

Privacy Policy of ShinStats: descriptives calc

Privacy Policy Shin Nix built the ShinStats app as an Ad Supported app. This SERVICE is provided by Shin Nix at no cost and is intended for use as is. This page is used to inform visitors regarding my policies with the collection, use, and disclosure of Personal Information if anyone decided to use my Service. If you choose to use my Service, then you agree to the collection and use of information in relation to this policy. The Personal Information that I collect is used for providing and improving the Service. I will not use or share your information with anyone except as described in this Privacy Policy. The terms used in this Privacy Policy have the same meanings as in our Terms and Conditions, which are accessible at ShinStats unless otherwise defined in this Privacy Policy. Information Collection and Use For a better experience, while using our Service, I may require you to provide us with certain personally identifiable information. The information that I request will be retaine...
Post a Comment
Read more

Power BI Journey: Blog #6

In this lesson, the focus is on "Conditional Formatting" which is very much similar to the conditional formatting in MS Excel which I could relate to following the making of the joint reporting system for personnel during my second job. Basically, we click the data columns that we want to be displayed in a tabular visualization.  Next, we select the columns that we will be applying conditional formatting to > Right-click on that column > Select Conditional Formatting > Select from among the options which is more appropriate for your application In this particular exercise, we utilized Background conditional formatting using gradient (applied on the first column of the first table) and rules (IF ELSE which was applied on the second column of the first table), Icons conditional formatting (applied on the second column of the first table), Data bars conditional formatting (applied on the second column of the first table and the fourth column of the second table). In the...
Post a Comment
Read more

SQL Journey: Blog #3

I have reached the end of lesson 2. The final project is entitled "Data dig" where we are given a set of interesting  data sets: NASA astronauts , Superbowl results , Pokemon stats , NBA players , Top movies , Top countries by population , Solar system objects by size , Marvel characters , Furniture store sales , Earned KA badges , Winston's donut logs , Card game results , and NFL draft picks . We are to pick one of those data sets and use advanced SELECT queries to discover things about the data. What sort of questions might one have about that data, like if they were using it for an app or a business idea? Here are some ideas:What are average, max, and min values in the data? What about those numbers per category in the data (using HAVING)? What ways are there to group the data values that don’t exist yet (using CASE)? What interesting ways are there to filter the data (using AND/OR)? Basically, the above given questions will serve as a guide on what we could dig from ...
Post a Comment
Read more