topic Re: How to set Default filter for Matrix or Table in DAX Commands and Tips

How to set Default filter for Matrix or Table

Madhu155154 — Tue, 13 Aug 2024 09:51:13 GMT

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

Re: How to set Default filter for Matrix or Table

Rupak_bi — Tue, 13 Aug 2024 12:50:50 GMT

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.

Re: How to set Default filter for Matrix or Table

ahadkarimi — Tue, 13 Aug 2024 19:52:29 GMT

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 )