Assignment # 6: Data Modelling using DAX Measures

#data #business #intelligence #datascience #powerbi

Nishit Joshi Feb 01 2021 · 2 min read
Share this

Introduction:

Animals, plants, and all living things are adapted to life in their natural surroundings. They range from burning hot, dry deserts, to rain-soaked tropical forests. Habitats are places in nature that provide food, protection from predators and unfavorable weather, and a home in which to raise young. Habitats are characterized most often by climate and location.

Objective:

We need to evaluate the segments based on terms of purchase and report the quantities available on the basis of animal if it’s affordable or expensive. We also need to evaluate the number of animals that don’t belong to Air, Water, and Land. Finally some segments needs to be made on basis of species by referring animals and town’s basis on regions.

Highlights:

Ø  Segment in term of Purchase (Affordable if<10 or Expensive if >10)

Ø  Quantities available on basis of Animal (Affordable or Expensive)

Ø  No of Animals don’t belong to Air, Water, & Land

Ø  Segment on basis of Species by referring Animals

Ø  Segment on towns on basis of region.

Source: LifeScience

1.)    Open Power BI and Extract data LifeScience.xlsx through Get Data by selecting Excel option. Select all the files and click on Load. 

2.)    Go to Data > Purchase Table > Click on New Column to use the DAX Function for Purchases to make it Affordable or Expensive.

Budget = IF(Purchase[Price]<10,"Affordable","Expensive")

3.)    Click on Repot > Select Clustered Bar Chart > Drag Quantity (Sum) to Values, Animal to Axis, and Budget to Legend and we can see the chart of Animals that’s Affordable or Expensive.

4.)    To find out how many animals do not belong to Air, Water, & Land. Go to Mode Table to find out the EnvironmentID and Name that can be use for DAX Functions to create separate measures for all three Environment.

Not Land = CALCULATE(COUNT('Product'[Animal]),Habitat[EnvironmentId]<>1)

Not Air = CALCULATE(COUNT('Product'[Animal]),Habitat[EnvironmentId]<>2)

Not Water = CALCULATE(COUNT('Product'[Animal]),Habitat[EnvironmentId]<>3)

5.)    To find out the segment on the basis of Species by the referring Animals you need to create a new column in the Product Table by using the following MAX function where RELATED Function is used to get the Species Name from Species table.

Species Segment = RELATED(Species[FamilyName])

6.)    To Segment the towns on the basis of region Go to Town Table and create the new column and use the following DAX Measures

Region = RELATED(Region[RegionName])

VISUALIZATION:

Ø  We have evaluated the affordable portion to be around 87 % compare to the Expensive portion of 12.76 %.

Ø  We have evaluated that Not Water animals are 14, Not Land Animals are 8, and Not Air Animals are 16.

Ø  We also used the segment for Towns on basis of Region and Spices on basis of their Family ID and name.

Comments
Read next