Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello,
I have a list of customers that are grouped into distributors:
Distributor | Customer |
Distributor A | Customer 1 |
Distributor A | Customer 2 |
OTHER | Customer 3 |
OTHER | Customer 4 |
OTHER | Customer 5 |
I created a measure to flag "Y" or "N" if customer have been active for the last 25 months. For every customer that doesn't belong to "OTHER" bucket the flag should say "Y" if the distributor is active even if customer isn't:
The problem with this formula is that if I filter a customer/distributor sales table on Active Flag = "Y" the Other bucket still shows sum of all customers even those that are not active. How to overcome that?
Distributor | Customer | Sales | Active Flag |
OTHER | Customer 3 | 200 | Y |
OTHER | Customer 4 | 200 | Y |
OTHER | Customer 5 | 100 | N |
Sum of all active customers should show 400, not 500:
Distributor | Sales | Active Flag |
OTHER | 500 | Y |
I can't create a calculated column for a flag as [Months Active] formula is dynamically changed by a slicer
"CALCULATE(IF([Months Active]>=25,"Y","N"),ALL(dim_distributor),VALUES(dim_distributor[Distributor])"
This doesn't really calculate much apart from the ALL() statement which is likely also the culprit. I think you meant ALLSELECTED().
Anyway, please provide sample data in usable format (not as a picture - maybe insert into a table?) including the Month Active column, and show the expected outcome.
User | Count |
---|---|
93 | |
83 | |
77 | |
73 | |
66 |
User | Count |
---|---|
115 | |
104 | |
93 | |
64 | |
61 |