/
Best Practices to Use Microsoft Power BI with Kyvos

Best Practices to Use Microsoft Power BI with Kyvos

Configure Batch Size for the Spark SQL Connector

When connecting to the Kyvos server using the Spark SQL connector, configure the Batch Size to optimize query performance. This setting must be applied consistently in both Power BI Desktop and Power BI Service during the connection configuration.
The recommended batch size is 200,000.

Here are the steps to configure the Batch Size when creating a connection using a Spark SQL Connector:

Using Power BI Desktop

  1. Search for the Spark SQL Connector in the Power BI Get Data window.

    Picture1.png

  2. Enter the required details to connect to the Kyvos Server.

    Picture3.png

  3. In the Advanced Options (Optional) section, locate the option to configure the Batch Size.

    Picture4.png

  4. Enter the value 200,000 in the provided field.

  5. After adding the Kyvos semantic model and creating the report in Power BI Desktop, ensure that when you publish the report to Power BI Service, the same configuration is applied in the data connection settings.

    1. Go to Settings > Manage Connections and Gateways.

      Picture5.png

    2. Create a new connection, similar to the one created in Power BI Desktop using the Spark SQL Connector.

      Picture6.png

    3. Ensure that the Batch Size is set to 200,000, as configured in Power BI Desktop.

Important

Whenever you publish a Power BI Desktop report to Power BI Service, you must configure the same Batch Size (200,000) in the corresponding dataset connection used by the report.

Use of the “Apply All Slicers” Button

When a user filters a report using multiple slicers, Power BI sends a query to the data source for each interaction. A recommended best practice is to use the Apply All Slicers button whenever possible. This allows users to first select the required filter values from the slicers and then click Apply All Slicers to send the queries in a single request.

To apply this configuration in Power BI, perform the following steps:

  1. Navigate to the Optimize tab in the Power BI toolbar.

    Picture7.png

     

  2. In this tab, locate the Apply All Slicers button.

  3. Click the button. Power BI will add the Apply All Slicers button to your report.

    Picture8.png

  4. After adding the button, whenever you interact with the slicers in the report, your selections will not be applied immediately. Instead, the slicers will display the message “Not yet applied.” This message indicates that the selected filters will only be applied after you click the Apply All Slicers button. Once the button is clicked, the selected filters are applied to the visuals, and the query is sent to the data source with the selected filters. For example, when the Apply All Slicers button is clicked, all selected slicer values are applied to the report visuals.

    Picture9.png

Removing the “Select All” Option in Slicer Selection

When a slicer uses a column with high cardinality, and users do not need to select all values at once, it is recommended to remove the “Select All” option from the slicer settings in Power BI.

To remove this option from the slicer settings in Power BI, follow these steps:

  1. Select the slicer visual in the report. Once selected, the Slicer Settings option will appear in the Visualization pane.

    Picture11.png

  2. Go to Slicer Settings and expand the Selection section.

    Picture12.png

  3. Locate the Show “Select all” option setting in the Selection section.

  4. Toggle off this option to remove the Select All option from the slicer.

You can also apply this change to multiple slicers at once:

  1. Press and hold the Ctrl key.

  2. Select all the slicer visuals in the report.

  3. Follow the same steps above to disable the “Select All” option for all selected slicers.

Use Calculated Tables for Slicers and Filters

Create calculated tables in Power BI that contain distinct or unique values for columns used in slicers and filters. These tables should be implemented as disconnected tables and used to drive report interactions.

To propagate the filter context from these disconnected tables to visuals, use Calculation Groups, which provide a controlled and efficient way to apply context without directly filtering large fact tables.

Follow the steps below to implement this approach:

  1. After connecting to the Kyvos data source, you will see the Model view in Power BI. This view contains all the columns and measures that will be used to create the Power BI report.

    Picture15.png

  2. Create a calculated table by selecting the New Table option from the Calculation tab in the Power BI toolbar.

    Picture16.png

  3. Use the following DAX expression to create a single-column calculated table that contains the distinct values of the selected column.
    In this example, the Category column is used. Replace it with the appropriate column name as required.

    DimCategory = FILTER(             DISTINCT('kyvos_nospark orderdate_valuebased'[category]),             NOT ISBLANK('kyvos_nospark orderdate_valuebased'[category]) )

  4. After creating the calculated table, you will see the updated Model view reflecting the newly created table.

    image-20260430-092605.png

     

  5. Next, implement a Calculation Group, which will be used to pass the filter context in the report so that the visuals are filtered accordingly.

  6. To create a Calculation Group, follow these steps:

    image-20260430-092659.png

    1. Locate the Calculation Groups section on the right side of the Model view in Power BI.

    2. Click New Calculation Group. A dialog box will appear on the screen.

    3. Click Yes in the dialog box to proceed. This indicates that calculated measures will be used when implementing the Calculation Group.

      image-20260430-092819.png

    Once created, the Calculation Group can be used to apply the required filter context to report visuals.

  7. Add the following expression into the bar as follows:

Note

Ensure that the above expression is created to handle one dimension only. If you want to include more than one dimension, repeat the steps to create additional calculated tables for each dimension. After creating the required tables, modify the expression accordingly as shown below.

Calculation item = VAR CategorySelected = ISFILTERED('DimCategory'[category]) RETURN CALCULATE( SELECTEDMEASURE(), FILTER( ALL('kyvos_nospark orderdate_valuebased'[category]), (CategorySelected && 'kyvos_nospark orderdate_valuebased'[category] IN                          VALUES('DimCategory'[category])) || NOT CategorySelected ) )

  1. Copy and paste the variable (VAR) creation expression multiple times based on the number of additional columns you want to include. For each column, you must create a separate variable using the same structure.

  2. Next, copy and paste the filter logic applied within the CALCULATE function and modify it to reference the newly created variable from the previous step. Add a comma after the previous filter logic to include the new condition.

    For example, when adding another column, the expression should be modified as shown below.

    CALCULATE( SELECTEDMEASURE(), FILTER( ALL('kyvos_nospark orderdate_valuebased'[category]), (CategorySelected && 'kyvos_nospark orderdate_valuebased'[category] IN VALUES('DimCategory'[category])) || NOT CategorySelected ), FILTER( ALL('kyvos_nospark orderdate_valuebased'[newcolumn]),              (NewColumnSelected && 'kyvos_nospark orderdate_valuebased'[newcolumn] IN VALUES('DimCategory'[newcolumn])) || NOT NewColumnSelected ) )

  3. Next, move to the Report View in Power BI and start building your report. Ensure that you implement the following steps in your report:

    image-20260430-093036.png

Use the Calculated Table Column in Slicers

  1. In the Data window, you will now see the Calculation Group column created earlier.

    image-20260430-093244.png

  2. Drag the Calculation Group column into the Filters pane and apply the filter as required.

    image-20260430-093321.png

  3. If your report contains multiple pages and slicers are used across these pages, place the Calculation Group column in the Filters on all pages section.

After completing these steps, you can create your visuals and select values from the slicers. The visuals will be filtered based on the selections made in the slicers.

Applying Default Filters to Measures for Load-Time Performance Optimization

In scenarios where measures return a very large number of rows, many of which may not be relevant to the end user, performance can be improved by applying a default filter at the Power BI layer.

This can be achieved by creating Power BI–level calculated measures that:

  • Respect the user-applied filter context when a slicer or filter is present.

  • Fall back to a predefined default filter when no slicer or filter is applied.

Implementing Default Filters Using DAX

Follow the steps below to implement a default filter on a measure using DAX in Power BI:

  1. Select the measure that you want to optimize in terms of filtered rows.
    For example, in this scenario we are targeting the Sum Sales measure.

    image-20260430-093507.png

  2. Create a calculated measure in Power BI using a DAX expression that applies a default filter to the selected measure.

  3. Note that the following DAX expressions are examples demonstrating how to apply default filters to a measure.
    You should modify the measure name, column names, and filter values according to your specific data model and requirements.

Case 1: Applying a Default Filter Based on a Single Column

Example: Country = United States

image-20260430-093623.png

Case 2: Applying a Default Filter Based on Two Columns

Example: Country = United States AND Category = Furniture

In both cases, the measure will apply the default filter when no user selection is present, while still respecting user-selected filters when applied through slicers or other report filters.

image-20260430-093857.png

Improve Load-Time Performance Using Default Measure Filters

In cases where measures return a very large number of rows—many of which may not be relevant to the end user—performance can be improved by applying a default filter at the Power BI layer. This is achieved by creating Power BI–level calculated measures that:

  • Respect user-applied filter context when present, and

  • Fall back to a predefined default filter when no slicer or filter is applied.

Here’s the implementation of setting up the default filter to the measure using DAX Calculation in the Power BI:

  1. Choose the Measure that you would like to optimize in terms of getting filtered rows. For example, here we’re targeting the ‘Sum Sales’ measure.

    image-20260512-122732.png

  2. Using the following DAX Expression, create a calculated measure inside Power BI which sets to default filter applied to the ‘Sum Sales’ Measure.

Note

This is an example DAX Expression where we’re going to implement the scenario of filtering measure. You’ll be creating your own scenario for the chosen columns and values that will be your default filter selection. This will vary depending upon the data. Within the following expression, you’ll need to change the measure, column name and values accordingly.

Case 1: If you want to set filter based on Single Column (Country = United States)

CalSales = VAR DefaultCountry = "United States"   VAR CountryOnAxis =     ISINSCOPE ( orderdate_valuebased[country] )   VAR CountryFilteredAny =     ISFILTERED ( orderdate_valuebased[country] )   VAR CountryFilteredExternal =     CountryFilteredAny && NOT CountryOnAxis   RETURN SWITCH (     TRUE (),       -- Country selected from slicer (not on axis)     CountryFilteredExternal,         SUM ( orderdate_valuebased[sum sales] ),       -- Country on axis OR no slicer → apply default     CALCULATE (         SUM ( orderdate_valuebased[sum sales] ),         KEEPFILTERS ( orderdate_valuebased[country] = DefaultCountry )     ) )

Case 2: If you want to set filter based on Two Column (Country = United States and Category = Furniture)

CalSales = VAR DefaultCategory = "Furniture" VAR DefaultCountry  = "United States"   -- CATEGORY FLAGS VAR CategoryOnAxis =     ISINSCOPE ( orderdate_valuebased[category] )   VAR CategoryFilteredAny =     ISFILTERED ( orderdate_valuebased[category] )   VAR CategoryFilteredExternal =     CategoryFilteredAny && NOT CategoryOnAxis   -- COUNTRY FLAGS VAR CountryOnAxis =     ISINSCOPE ( orderdate_valuebased[country] )   VAR CountryFilteredAny =     ISFILTERED ( orderdate_valuebased[country] )   VAR CountryFilteredExternal =     CountryFilteredAny && NOT CountryOnAxis   RETURN SWITCH (     TRUE (),       -- Both Category & Country filtered from slicers     CategoryFilteredExternal && CountryFilteredExternal,         SUM ( orderdate_valuebased[sum sales] ),       -- Category external, Country not external → apply default Country     CategoryFilteredExternal && NOT CountryFilteredExternal,         CALCULATE (             SUM ( orderdate_valuebased[sum sales] ),             KEEPFILTERS ( orderdate_valuebased[country] = DefaultCountry )         ),       -- Country external, Category not external → apply default Category     NOT CategoryFilteredExternal && CountryFilteredExternal,         CALCULATE (             SUM ( orderdate_valuebased[sum sales] ),             KEEPFILTERS ( orderdate_valuebased[category] = DefaultCategory )         ),       -- Neither externally filtered → apply both defaults     CALCULATE (         SUM ( orderdate_valuebased[sum sales] ),         KEEPFILTERS ( orderdate_valuebased[category] = DefaultCategory ),         KEEPFILTERS ( orderdate_valuebased[country]  = DefaultCountry )     ) )

Step 3: Use this Calculated Expression into your visuals as follows:

Case 1: Using Single Column Filter

image-20260512-122839.png

Case 2: Using Two Column Filters

image-20260512-122940.png

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Copyright Kyvos, Inc. 2026. All rights reserved.