Hide Data at a Certain Matrix Table Level

[PBIMike]

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

[amitchandak]

@PBIMike , see if this can help

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/

[DouweMeer]

@PBIMike 

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

What is your measure for Sales type?

[PBIMike]

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

[DouweMeer · Accepted solution]

Probably someting like the following would work:

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

[PBIMike]

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)

[DouweMeer]

@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. 

[PBIMike]

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