How to display values of a Selected Field in the Slicer using Field Parameters in Power BI

How to display values of a Default Field in the Slicer using Field Parameters in Power BI

Scenario:
When a Field is selected from Field Parameter Slicer, then we want to display the values of that Selected Field in the Slicer.

Example: 

When a [Product] dimension is selected then we want to display the values of that dimension {Radio, Fridge, Television etc}.

When no Dimension is selected then we want to display the values of the first Dimension in the Field Parameters.

Example: 

Say [Month-Year] is the first dimension in the Field Parameters list, then we want to display the values of that dimension {Jan-2024, Feb-2024, March-2024 etc}.

We can achieve this Scenario, as explained below.

1) Create a Field Parameters with a list of Dimensions:

First, we need to create a Field Parameters table with required Dimensions as per below.

fp_Dim_Selector = {

    ("Month-Year", NAMEOF('dim_Calendar'[Month_Year]), 1,"Month_Year"),

    ("Region", NAMEOF('dim_Region'[Region_Name]), 2,"Region_Name"),

    ("Country", NAMEOF('dim_Country'[Country_Name]), 3,"Country_Name"),

    ("Customer", NAMEOF('dim_Customer'[Customer_Name]), 4,"Customer_Name"),

    ("Product", NAMEOF('dim_Products'[Product_Name]), 5,"Product_Name")

}

2) Create the Slicers for Dimension and Its Values:

We can create the "Dimension" Slicer using Field_Name as Source.

Next we can create the "Dimension Details" Slicer using Field_Name and enabling the option to "Show values of selected field" by right clicking on Field_Name.

In general, the Details Slicer will show values of the Selected Dimension. However, when No Dimension is selected from Dimension Slicer, then the Details Slicer will fail to show the values, as there is no Dimension Selected, as shown below:

Instead of showing Error, by default we want to display the values of first Dimension Month-Year {Jan-2024, Feb-2024, March-2024 etc}.

This can be achieved as explained in the next step.

3) By default, display the Values of first Dimension:

To achieve this, we can create a below Measure to capture the Minimum Index Number of the Dimension.

Selected Min Dim Index =

VAR _Dim_Index = {1,2,3,4,5}

VAR _Min_Dim_Index =

CALCULATE(MIN(fp_Dim_Selector[Field_Index]),

    fp_Dim_Selector[Field_Index] IN _Dim_Index

    )

RETURN

_Min_Dim_Index

Next, we need to use this Measure as a Visual Level filter for Details Slicer, using Top N and Bottom 1 filter method as shown below:

Result:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to load data from a Data Warehouse in Azure Databrics Catalog using Power BI

How to load data from a Data Warehouse in Azure Databrics Catalog with Native SQL Query using Power BI

Unity Catalog:
It is a data governance solution in Databricks that provides centralized access control, auditing, lineage tracking, and data discovery across multiple workspaces. It ensures secure and organized data management.

Catalog:

It is a top-level container within Unity Catalog that holds multiple Databases (Schemas). It helps in data isolation and logical organization.
Example: A SalesData catalog may contain databases like Retail, Wholesale, E-commerce.

Databricks primarily operates on a Lakehouse architecture, which combines elements of data lakes and data warehouses. While Databricks SQL Warehouses are used for querying and analytics, Databricks also supports Databases (Schemas) within Unity Catalog for structured data organization.

SQL Warehouses: 

Compute resources optimized for running SQL queries on structured data. Supports BI tools, Delta Lake integration, and fast query execution.

Databases (Schemas): 

The Database (Schema) exists inside a Catalog and is used for logical grouping of related data. It contains tables, views, functions, and models.
Example: Inside the Retail database, you might store tables like Customers, Orders, and Products.

Notes:
When connecting to Databricks Unity Catalog, Power Query may flatten the hierarchy and display the Catalog as a Database.
This happens because traditional databases (like SQL Server) don’t have a Catalog layer, so Power Query maps the Catalog to a Database for compatibility.

Since Databricks Unity Catalog introduces a three-tier hierarchy (Catalog → Database → Table/View), Power Query may simplify this by treating the Catalog as a Database.

Databricks does not function like a traditional database system (such as SQL Server or PostgreSQL), but it does provide database-like structures within Unity Catalog for managing data efficiently.

--------------------------------------------------------------------------------------------------

Scenario:

To connect and load data from a Data Warehouse in Azure Databrics Catalog, we need the following details like, Server Hostname, HTTP Path, which are mandatory.

I have defined the Parameters in the Power Query to hold the Server Hostname, HTTP Path, Catalog/DB Name, and Schema name as shown below samples (Dummy values).

We will use these parameters in the following examples. Please make sure to pass your own correct values for these parameters while you are testing.

p_ServerHostName =  adb-server_host_id.3.azuredatabricks.net

p_http_Path = sql/protocolv1/o/server_host_id/cluster_id

p_Catalog_DB = MyCatlog_db

p_Schema = MySchema

p_Load_By_Year = 2021, This Parameter is used to limit the Data Load from the Query. 

Notes:

In the Azure Databrics Workspace, we can find the Server Hostname and HTTP path values from the following ways:

Compute >[Cluster_Name] > Advanced Options > JDBC/ODBC:

Server hostname: adb-server_host_id.3.azuredatabricks.net

HTTP path: sql/protocolv1/o/server_host_id/cluster_id

SQL Warehouses> [MyWarehouse_Name] > Connection Details:

Server hostname: adb-server_host_id.3.azuredatabricks.net 

HTTP path: /sql/1.0/warehouses/warehouse_id

1) Loading a View or Table from the Data Warehouse in Azure Databrics Catalog:

In Power BI, we use default "Azure Datarbics" connector to connect and load a View or Table from a Data Warehouse in Azure Databrics Catalog.

We need to pass only the Server Hostname and HTTP Path as shown below.

After Connecting to the Source, it will open a dialogue box with available Catalogs and their underlying Warehouses/Databases and Objects to select.

The following is the sample Power Query logic of an object (View) loaded, which is updated with Parameters defined above:

let

    Source = Databricks.Catalogs(p_ServerHostName,p_http_Path, [Catalog=null, Database=null, EnableAutomaticProxyDiscovery=null]),

    Src_Database = Source{[Name=p_Catalog_DB,Kind="Database"]}[Data],

    Src_Schema = Src_Database{[Name=p_Schema,Kind="Schema"]}[Data],

    Src_Object = Src_Schema{[Name="MyViewName",Kind="View"]}[Data]

in

    Src_Object

Note:
If you want to load Table instead of View, change the Kind="View" to Kind="Table"

2) Loading data using a Native SQL Query from the Data Warehouse in Azure Databrics Catalog:

To connect and load data by running a Native SQL Query from the Data Warehouse in Azure Databrics Catalog, we need to pass the Server Hostname, HTTP Path and Catalog Name are mandatory.

Please make sure include the Schema.Table/View Name in the Native SQL Query section.

The following is the sample Power Query logic of data loaded using Native SQL Query, which is updated with Parameters defined above:

let

    vCY = Date.Year(DateTime.LocalNow()),

    vYearsList = " IN (" & Number.ToText(vCY) & "," & Number.ToText(vCY-1) & "," & Number.ToText(vCY-2) & ")", 

    vLoadYears = if p_Load_By_Year is null then vYearsList else ">= " & Number.ToText(p_Load_By_Year),

    vSrc_Qry = "SELECT transact_id, transact_date, field3, field4, field5, fieldN

    FROM " & p_Schema & ".fact_Transactions fact

    Where YEAR(fact.transact_date) " & vLoadYears,

    Source = Value.NativeQuery(Databricks.Catalogs(p_ServerHostName, p_http_Path, 

    [Catalog=p_Catalog_DB, Database=null, EnableAutomaticProxyDiscovery=null]){[Name=p_Catalog_DB,Kind="Database"]}[Data], 

    vSrc_Qry, null, [EnableFolding=true]),

    ChangType = Table.TransformColumnTypes(Source,{{"transact_date", type date}})

in

    ChangType

Notes:
p_Load_By_Year is a Year Parameter is used to limit the Data Load from the Query. If this Parameter is Blank (), then the Data will be loaded by default for last 3 Years (from Current Year). Otherwise, it loads the Loads the data >= 2021 (Year value from Parameter)

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------

How to calculate Total No. of Tasks Active at the end of Selected Period using DAX in Power BI

How to calculate Total No. of Active Tasks during the Selected Period using DAX in Power BI

Scenario:
Let's say we have the Tasks Summary dataset with details like [No. of Tasks] as of [Task Start Date] and their [Task End Date].

The sample data in the Dataset, with No. of Tasks and their Start Dates and End Dates are as follows:

There is a one 1 task with start date as 01-Jan-2021 and end date as 05-Jan-2021.

There are 5 tasks with start date as 03-Jan-2021 and end date as 10-Jan-2021.

There is one 1 task with start date as 03-Jan-2021 and end date as 07-Jan-2021.

There are 4 tasks with start date as 04-Jan-2021 and end date as 07-Jan-2021.

There are 3 tasks with start date as 05-Jan-2021 and end date as 12-Jan-2021.

There are 2 tasks with start date as 06-Jan-2021 and end date as 15-Jan-2021.

There are 4 tasks with start date as 07-Jan-2021 and end date as 18-Jan-2021.

There are 3 tasks with start date as 08-Jan-2021 and end date as 11-Jan-2021.

There are 4 tasks with start date as 08-Jan-2021 and end date as 12-Jan-2021.

There are 4 tasks with start date as 08-Jan-2021 and end date as 20-Jan-2021.

There is one 1 task with start date as 12-Jan-2021 and end date as 23-Jan-2021.
There is one 1 task with start date as 12-Jan-2021 and end date as 26-Jan-2021.
There is one 1 task with start date as 12-Jan-2021 and end date as 19-Jan-2021.

From this Sample, we understand that some tasks are starting on a Start Date and some tasks are close on the same Start Date.

Examples:
On Task_Start_Date: 05-Jan-2021, 3 new tasks are Starting and 1 previous task is Closing as its Task_End_Date is 05-Jan-2021.

Similarly, on Task_Start_Date: 07-Jan-2021, 4 new tasks are Starting and 5 previous tasks are Closing as their Task_End_Date is 07-Jan-2021.

Similarly, on Task_Start_Date: 12-Jan-2021, 3 new tasks are Starting and 7 previous tasks are Closing as their Task_End_Date is 12-Jan-2021.

With this understanding, now we will calculate the Total No. of Tasks that are active during the Selected Period, and the actual No. of Tasks active at the end of the Selected Period using the below DAX Logics based on the following Data Model:

[No. of Tasks on StartDate] = SUM(Tasks_Summary[No_of_Tasks])

The following Measure returns the Cumulative Total No. of Tasks by dim_Date[Date]:

Cumulative No. of Tasks on StartDate =

VAR _MaxSelectDate = MAX(dim_Date[Date])

VAR _Cumulative_Tasks =

CALCULATE( [No. of Tasks on StartDate],

    dim_Date[Date] <= _MaxSelectDate,

    REMOVEFILTERS(Tasks_Summary)    

    )

RETURN

IF(NOT ISBLANK([No. of Tasks on StartDate]),_Cumulative_Tasks)

The following measure returns "Y", if any Tasks are Closing on that selected Task_Start_Date:

Is any Task Closing on Selected StartDate =

VAR _Max_Date = MAX(Tasks_Summary[Task_StartDate])

VAR _Min_Task_Date =

CALCULATE(

        MIN(Tasks_Summary[Task_EndDate]),

        FILTER(

            ALL(Tasks_Summary),

            Tasks_Summary[Task_EndDate] = _Max_Date

        )

    )

VAR _Result = IF ( NOT ISBLANK(_Min_Task_Date),"Y","N")

RETURN

IF( NOT ISBLANK(_Max_Date), _Result)

The following measure returns No. of Tasks that are Closing on that selected Task_Start_Date:

No. of Tasks Closing on Selected StartDate =

VAR _Max_Date = MAX(Tasks_Summary[Task_StartDate])

VAR _Result =

    CALCULATE(

        [No. of Tasks on StartDate],

        FILTER(

            ALL(Tasks_Summary),

            Tasks_Summary[Task_EndDate] =_Max_Date

        )

    )

RETURN

IF( NOT ISBLANK(_Max_Date), _Result)

The below measure returns the total No. of Active Tasks including Closed tasks during the selected Date Period.

No. of Tasks Active during the Selected Period =

VAR _MinSelectDate = MIN(dim_Date[Date])

VAR _MaxSelectDate = MAX(dim_Date[Date])

VAR _MaxTaskDate = MAX(Tasks_Summary[Task_StartDate])

VAR _Result =

    CALCULATE(

        [No. of Tasks on StartDate],

        REMOVEFILTERS(dim_Date[Date]),

        Tasks_Summary[Task_StartDate] <= _MaxSelectDate,

        (

        Tasks_Summary[Task_EndDate] > _MinSelectDate

        ||

        ISBLANK(Tasks_Summary[Task_EndDate])

        )

    )

RETURN

IF(NOT ISBLANK(_MaxTaskDate),_Result)

The below measure returns the total No. of Active Tasks excluding Closed tasks at the end of the selected Date Period.

No. of Tasks Active at the end of Selected Period =

VAR _MaxSelectDate = MAX(dim_Date[Date])

VAR _MaxTaskDate = MAX(Tasks_Summary[Task_EndDate])

VAR _Result =

    CALCULATE(

        [No. of Tasks on StartDate],

        REMOVEFILTERS(dim_Date[Date]),

        Tasks_Summary[Task_StartDate] <= _MaxSelectDate,

        (

        Tasks_Summary[Task_EndDate] > _MaxSelectDate

        ||

        ISBLANK(Tasks_Summary[Task_EndDate])

        )

    )

RETURN

IF( NOT ISBLANK(_MaxTaskDate), _Result )

Result:

The result of the above Measures are as per below:

The fields [Date], [Month_Year] are considered from Dim_Date table.

The fields [Task_StartDate] and [Task_EndDate] are considered from Task_Summary table.

The results of the following measures are seems to be similar in the above Table visual. However, when we look at the Totals, they returns different results.

[No. of Tasks Active during the Selected Period] : It Sum up all the Active Tasks, including all the Closed tasks and returns the total no. of tasks during the selected date period.
[No. of Tasks Active at the end of Selected Period] : It Sum up the Active Tasks, excluding all the Closed Tasks and returns the final total no. of tasks at the end of the selected date period.

The result of Total No. of Tasks from the above measure can be viewed using Column Charts as shown below:

--------------------------------------------------------------------------------------------------------

Thanks, TAMATAM ; Business Intelligence & Analytics Professional

--------------------------------------------------------------------------------------------------------