How to Build a dashboard with dynamic view via MS Power BI?

Purpose: To present different perspectives and layers of data in just one page instead of jumping into different pages back and forth across the tabs in MS Power BI.

Tool:
— Dashboard: MS Power BI
— Backend DB: MS SQL Server

Steps:

1. Import data from [Simulation].[001_Product_Loc_Demand] stored in the server into Power BI called Query1.

SELECT * FROM [Simulation].[001_Product_Loc_Demand]

2. In Power BI, we have to create a dummy table for attribute selection.

3. Create a measure called Selection_Metric in the main table (Query1). This can return a specific value based on the value user selects. For example, if user selects “Units”, this measure will return “Units” back so that we can control which field we want to present in the dashboard (See point 4).

Selection_Metric = IF(HASONEVALUE('Selection Table'[Display]),VALUES('Selection Table'[Display]),BLANK())

4. Create a measure called Display_Metric in the main table (Query1). This can control the field we want to present in the bar chart below.

Display_Metric = IF([Selection_Metric]="Units",SUM(Query1[Demand]), IF([Selection_Metric]= "Sales",SUM(Query1[Sales]), IF([Selection_Metric]= "Margin",SUM(Query1[Margin]))))

5. In the bar chart, drag Dispay_Metric into value and you can rename it if you want.