cancel
Showing results for
Did you mean:

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Anonymous
Not applicable

## Auto-Filter Values in Matrix in different levels in Hierarchy

Hi,

I have a matrix with a two variables in the rows.  The first level variable is year. The 2nd variable is month but i only want the values to display data for the current year.

For example - at level 1 the row will be years, and the values will by confirmations for all those years. At level 2, the rows will be months, and the values will be confirmations for the current year only.

Is it possible to only filter for the current year when i have month as the rows in the hirarchy?

1 ACCEPTED SOLUTION
Community Support

Hi @Anonymous ,

To create a meausre as below.

```Measure =
VAR sum1 =
CALCULATE ( SUM ( Table1[Confirmations] ) )
VAR filtersum =
CALCULATE (
SUM ( Table1[Confirmations] ),
FILTER ( Table1, Table1[PIT_IND] = "TY" )
)
RETURN
IF ( ISINSCOPE ( Table1[Region] ), filtersum, sum1 )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
3 REPLIES 3
Community Support

Hi @Anonymous ,

Could you please share your sample data and excepted result to me if you don't have any Confidential Information.Please upload your files to One Drive and share the link here.

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
Anonymous
Not applicable

Hi,

I inserted 3 tables below. The first table would hypothetically be the default view of the matrix (This data is unfiltered). The second below table would be the matrix, but drilled down one level(This data would be filtered for PIT_IND=TY)

The 3rd below table is my dataset.

Does this make sense?

Level 1: Data isn't Filtered:

 Year Confirmations 2014 1,532 2014 PIT 1,235 2015 309 2015 PIT 813 2016 1,989 2016 PIT 717 2017 758 2017 PIT 110 2018 2,195 2018 PIT 1,801 2019 2,733 2019 PIT 3,090 Grand Total 17,282

Level 2 (Filtered for PIT_IND=TY)

 Region Confirmations Canada 376 East Coast 3,713 Europe 0 Mexico 659 Mid West 2,713 West Coast 2,056 Grand Total 9,516

My Dataset:

 Year Region PIT_IND Confirmations 2014 East Coast TY 957.8221 2014 West Coast TY 574.4138 2015 East Coast TY 309.0494 2016 East Coast TY 233.478 2016 West Coast TY 902.912 2016 Mid West TY 852.4647 2017 East Coast TY 758.103 2018 East Coast TY 957.1982 2018 West Coast TY 28.61213 2018 Mid West TY 925.1769 2018 Mexico TY 283.9663 2019 East Coast TY 496.9914 2019 Mid West TY 935.6592 2019 West Coast TY 549.7434 2019 Mexico TY 374.5806 2019 Canada TY 375.7129 2019 Europe TY 0.392929 2014 PIT East Coast LY 333.7524 2014 PIT West Coast LY 901.5507 2015 PIT East Coast LY 812.9767 2016 PIT East Coast LY 82.70674 2016 PIT West Coast LY 196.2866 2016 PIT Mid West LY 438.4759 2017 PIT East Coast LY 109.5359 2018 PIT East Coast LY 632.1703 2018 PIT West Coast LY 359.0873 2018 PIT Mid West LY 747.6088 2018 PIT Mexico LY 62.25398 2019 PIT East Coast LY 289.9601 2019 PIT Mid West LY 742.7774 2019 PIT West Coast LY 305.5905 2019 PIT Mexico LY 363.5127 2019 PIT Canada LY 624.2058 2019 PIT Europe LY 763.5378
Community Support

Hi @Anonymous ,

To create a meausre as below.

```Measure =
VAR sum1 =
CALCULATE ( SUM ( Table1[Confirmations] ) )
VAR filtersum =
CALCULATE (
SUM ( Table1[Confirmations] ),
FILTER ( Table1, Table1[PIT_IND] = "TY" )
)
RETURN
IF ( ISINSCOPE ( Table1[Region] ), filtersum, sum1 )
```

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

Announcements

#### Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

#### Power BI Monthly Update - April 2024

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

#### Fabric Community Update - April 2024

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

Top Solution Authors
Top Kudoed Authors