Forum Discussion
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
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])))
if this solves your purpose, please accept this as solution.
2 Replies
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])))
if this solves your purpose, please accept this as solution.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 )
