Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
Probably someting like the following would work:
measure = if ( countrows ( distinct ( [salestype] ) ) > 1 , "Mix" , min ( [salestype] ) )
@PBIMike , see if this can help
https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/
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)
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 🙂
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |