cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper I

## How to set Default filter for Matrix or Table

Hi everyone, I want a matrix to show values for a particular filter, L1, from KPI Level by default.

Note:  I will use KPI Level as a slicer. When I click on the L2 slicer, the table should display L2 data. For L3 selection, it should show L3 data, and for L1, it should show L1 data.

If no filters from the KPI Level slicer are selected, the table should display only L1 data by default.

The table needs to include the following columns:

- KPI Level
- Region
- Category
- Target
- Actual

Below is the data view of the dataset:

ChatGPT data prompt:  https://chatgpt.com/share/b2a755d3-e928-4817-80ef-a643de3c6bf0

Dax formula i used:

Defualt_Target =

Var _a = SELECTEDVALUE(Data[KPI Level], "L1")

Var _b = CALCULATE(sum(Data[Target]), FILTER(Data,
Data[KPI Level]= _a ))

RETURN _b

I am not able to acheive the result what i wanted

1 ACCEPTED SOLUTION
Responsive Resident

Hi Based on my understanding, here is my solotion

Approach I used here is  -
1. created a new disconnected table with KPI values
2. switched the output based on KPI filters. formula as below:

sum of target =
SWITCH(TRUE(),SELECTEDVALUE('Table (2)'[KPI Level],"L1")=max('Table (3)'[new kpi]),calculate(sum('Table (2)'[Target]),'Table (2)'[KPI Level]=max('Table (3)'[new kpi])))

2 REPLIES 2
Solution Specialist

@Madhu155154create the two measures below, try them out, and let me know if there are any issues.

Filtered_Target =
VAR SelectedKPI = SELECTEDVALUE(Data[KPI Level], "L1")
RETURN
CALCULATE(
SUM(Data[Target]),
Data[KPI Level] = SelectedKPI
)
Filtered_Actual =
VAR SelectedKPI = SELECTEDVALUE(Data[KPI Level], "L1")
RETURN
CALCULATE(
SUM(Data[Actual]),
Data[KPI Level] = SelectedKPI
)

Responsive Resident

Hi Based on my understanding, here is my solotion

Approach I used here is  -
1. created a new disconnected table with KPI values
2. switched the output based on KPI filters. formula as below:

sum of target =
SWITCH(TRUE(),SELECTEDVALUE('Table (2)'[KPI Level],"L1")=max('Table (3)'[new kpi]),calculate(sum('Table (2)'[Target]),'Table (2)'[KPI Level]=max('Table (3)'[new kpi])))

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors