Skip to main content
cancel
Showing results for 
Search instead 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.

Reply
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

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 )

Capture.PNG

 

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

View solution in original post

3 REPLIES 3
v-frfei-msft
Community Support
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)

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

My Dataset:

YearRegionPIT_INDConfirmations
2014East CoastTY957.8221
2014West CoastTY574.4138
2015East CoastTY309.0494
2016East CoastTY233.478
2016West CoastTY902.912
2016Mid WestTY852.4647
2017East CoastTY758.103
2018East CoastTY957.1982
2018West CoastTY28.61213
2018Mid WestTY925.1769
2018MexicoTY283.9663
2019East CoastTY496.9914
2019Mid WestTY935.6592
2019West CoastTY549.7434
2019MexicoTY374.5806
2019CanadaTY375.7129
2019EuropeTY0.392929
2014 PITEast CoastLY333.7524
2014 PITWest CoastLY901.5507
2015 PITEast CoastLY812.9767
2016 PITEast CoastLY82.70674
2016 PITWest CoastLY196.2866
2016 PITMid WestLY438.4759
2017 PITEast CoastLY109.5359
2018 PITEast CoastLY632.1703
2018 PITWest CoastLY359.0873
2018 PITMid WestLY747.6088
2018 PITMexicoLY62.25398
2019 PITEast CoastLY289.9601
2019 PITMid WestLY742.7774
2019 PITWest CoastLY305.5905
2019 PITMexicoLY363.5127
2019 PITCanadaLY624.2058
2019 PITEuropeLY763.5378

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 )

Capture.PNG

 

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

Helpful resources

Announcements
Microsoft Fabric Learn Together

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

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

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

April Fabric Community Update

Fabric Community Update - April 2024

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