power bi DAX, drill through & parameter

The basic DAX syntax is as follows.

There are a bunch of functions, which come with round brackets ()

Table name can be used directly. The column is referenced by square brackets []

Create DATE table from DAX

The CALENDARAUTO  function simply return a table with one column 'Date' that contains a range of dates.

The ADDCOLUMNS function derives more columns, such as year, month, week, etc. from the 'Date' column using different format, week, month functions.

Calendar = ADDCOLUMNS (CALENDARAUTO (),

    "Year", FORMAT([Date], "yyyy"),

    "MonthNo", MONTH([Date]),

    "Month", FORMAT([Date],"MMM"),

    "Quarter", FORMAT([Date],"\QQ"),

    "YearMonth", FORMAT([Date],"YYYY-MM"),

    "WeekdayNo", WEEKDAY([Date],2),

    "Weekday", FORMAT([Date],"ddd"),

    "WeekNo", WEEKNUM([Date], 2),

    "Week", "W" & WEEKNUM([Date], 2))

CACULATE function

Evaluates an expression in a modified filter context. e.g.

Revenue AC = CALCULATE(sum(Fact_Sales[Revenue]), FILTER(Fact_Sales, Fact_Sales[Scenario]="AC"))

This returns the sum of Revenue from the Fact_Sales table, in the context of Scenario = 'AC'

Drill Through

Very often a report needs to drill through to a detailed page.

So create a detail page, e.g. with a table of raw records. 

It needs to set the drill through column so it knows where the drill through can be coming from.

For example, here the Revenue PL minus Revenue AC column from other page can drill through to here.

To drill through, right lick the bar charts below for Revenue PL minus Revenue AC, and click drill through

Note the drill through carries on all the filter context, i.e. 2018, Personal Care (Baby, hair) and Nov column to the detail page.

Report parameter

This is pretty much the same as Slicer.

You can createa Numeric Range slicer, or a Field selection list as parameter.

Then in DAX, it can refer to the range paramter value using Parameter[Parameter Value]

If it is field parameter, then the visual can point to the parameter (e.g. set Y axis to Parameter). When a field is selected, the visual is updated to display the selected field (column).