Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
spitfyah
Frequent Visitor

Show Value only in second level of matrix

Hi All,

 

I have a matrix with the following rows in a hierarchy:

 

Client

MonthYear

Office

Agent

Recipient ID

 

I want to make a measure to display the target but only at the MonthYear level so it would show something like this:

 

Client   Target

Dudes

Gents

 

Client                  Target

Dudes

   Oct 2021              60

   Nov 2021              40

Gents

   Oct 2021              80

   Nov 2021             21

 

 

Client                               Target

Dudes

   Oct 2021

         Chicago Office

         London Office      

   Nov 2021

         Chicago Office

         London Office   

              

Gents

   Oct 2021

            Beijing Office

             Sydney Office

   Nov 2021                

            Beijing Office

             Sydney Office

 

 

Targets are only at a client level but are updated every month. How would I go about achieving this?

 

I think I have gotten a measure that calculates it, but so far it does it all the way down the hierarchy when really I only want it done on the rows with MonthYear

 

 

if(not(isinscope(Dim_Date[YearMonth])),target (variable I created to work out the target), BLANK()) 
 
if(isinscope(Dim_Date[YearMonth]),target (variable I created to work out the target), BLANK()) 
 
if(isblank(SELECTEDVALUE(Dim_Date[YearMonth])), BLANK(),target)
 
 
How would I get this to work?

 

 

 

 

        

 

 

 

 

1 ACCEPTED SOLUTION
v-yalanwu-msft
Community Support
Community Support

Hi, @spitfyah ,

I test a simple example. and you could refer it.

Measure = IF(ISINSCOPE('Table'[Client]),IF(ISINSCOPE('Table'[Date]),SUM([Target])))

The final show:

vyalanwumsft_0-1653029921431.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

2 REPLIES 2
v-yalanwu-msft
Community Support
Community Support

Hi, @spitfyah ,

I test a simple example. and you could refer it.

Measure = IF(ISINSCOPE('Table'[Client]),IF(ISINSCOPE('Table'[Date]),SUM([Target])))

The final show:

vyalanwumsft_0-1653029921431.png


Best Regards,
Community Support Team _ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

lbendlin
Super User
Super User

you're on the right track with ISINSCOPE() but you may also have to add NOT ISINSCOPE() for the other levels.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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