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
PBIMike
Helper III
Helper III

Hide Data at a Certain Matrix Table Level

Hello,

 

I have a table with 1 lvl of drilldown, eg:

 

Customer                   Value                    Sale Type

A                                 100                       xxxx

--> order 1                 40                         New

--> order 2                 60                         Upsell

B                                 200                       xxxx

--> order 1                 140                        New

--> order 2                 60                         Upsell

 

I would like the result under Sale Type at the customer level to be nil. Currently, I only have the 'first' or 'last' option, meaning Sale Type against customer A would be either New or Upsell, but really it's a mix so I just want it to be blank.

 

The following link looked to be inthe right direction, but I think this only works for values?

https://powerpivotpro.com/2013/03/hasonevalue-vs-isfiltered-vs-hasonefilter/

 

 

Many thanks,

Mike

1 ACCEPTED SOLUTION

Probably someting like the following would work:

measure = if ( countrows ( distinct ( [salestype] ) ) > 1 , "Mix" , min ( [salestype] ) )

View solution in original post

7 REPLIES 7
amitchandak
Super User
Super User
DouweMeer
Post Prodigy
Post Prodigy

@PBIMike 

Looks like a realization report I've finished earlier where we split maintained with won business. 

What is your measure for Sales type?

No measures. Sales Type is just directly pulling from a column in excel data.

Probably someting like the following would work:

measure = if ( countrows ( distinct ( [salestype] ) ) > 1 , "Mix" , min ( [salestype] ) )

Thank you, but to be honest I'm not sure how to create a measure in that manner. It sounds like you attach it to a field. However, I only know how to create a measure as a field in its own right currently.

 

Presumably I'm attaching the measure to the "Customer" field somehow, so that if the measure criteria is met the Sales Type field is overriden? (to be "mix" in your example below)

@PBIMike 

I'm just lazy in the comment section. Replace [Salestype] by a 'Table'[Salestype]. 

But the return value of a measure should always be a single value. When you apply the min expression, a string value can be returned. 

Thank you! I've been working using data rather than measures too often - will try and change that 🙂

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.