DYNAMIC COLUMN SELECTION USING POWERBI PARAMETERS

Dynamic Column Selection using Power BI Parameters allows users to interactively choose which column of data they want to analyze or visualize by using what-if parameters or field parameters. This technique creates flexibility in reports by enabling users to change measures or dimensions shown in charts without modifying the report itself. to do this;

1. Open your powerBI desktop and load the sample data set (default data set)

2. Select the financials and load it 

3. The data will be previewed and it will appear under the data section at the right
4. We will add a new table to create a measure that will get the sum of the columns sales, profit, discount and gross sales.
5. To do this, click on enter data

6. In the bar that will appear, rename it to measure and load

7. It has been added to the data session. Click on the three dots on the measure and select new measure.

8. Click on the new measure created and in the formula bar, write a formula for sum of sales and enter. That is, sales = SUM(financials(sales))

9. You will see the measure name change to sales. Click on the three dots on column 1 above and in the bar that will appear, select delete from model. The table will now remain as a measure table.

10. For profit, Click on the three dots on the measure and select new measure.
11. Click on the new measure created and in the formula bar, write a formula for profit and enter. That is  profit = SUM(financials(profit))

12. For discount, Click on the three dots on the measure and select new measure.
13. Click on the new measure created and in the formula bar, write a formula for profit and enter. That is  discount = SUM(financials(discount))

14. For gross sales, Click on the three dots on the measure and select new measure.
15. Click on the new measure created and in the formula bar, write a formula for profit and enter. That is  gross sales = SUM(financials(gross sales))

16. After getting your measures into the measure table, we navigate to the modelling tab.

17. Go to parameter, on the dropdown bar, click on fields parameter.

18. Select measures and drag them into the Add and Reorder fields. Bring sales, profit, discount and gross sales and hit the create button. It will automatically add it to a powerbi slicer.

19. Drag the slicer, go to build visual, select the middle tab.

20. Click on the slicer settings, change the style to Tile and you will see our slicer displayed as tile.

21. To get a stacked bar chart, click on the stacked bar chart in visualization

22. We can be able to do sales, profit, discount and gross sales in one axis. To do this, click on parameter

23. Go to table view, click on the eye icon on parameter and unhide it so that we can get the parameter field.

24. Once you do that, it will appear in the report view.

25. In the report view, drag the parameter field into the x axis 

26. In the data, go to the financials and select product, get it into the y axis. The sales, product, discount and gross sales are now in one axis.

27. We can now select the names in the tiles to see each chart for sales, product, discount and gross sales.

28. Add a conditional statement so that each category can display a different chart colour. To do this, click on the created stack bar chart, click on format visual and scroll down to the bars. Click on fx (conditional formatting)

29. Go to what field should we base this on and select parameter

30. Go to the section of if value is and edit the text to sales, select colour of your choice. In this example, we make use of the default colour, sky blue.

31. Go to new rule to add another rule.

32. If value is, edit the text to  profit, select green colour

33. Add new rule, if value is discount, select colour of your choice

34. Add new rule, if value is gross sales, select colour and click on ok

35. Navigate through and you will notice that the colours of the carious charts will change