0

DimGroupCustomer Table

╔═══════════════╦═══════════╦══════════════╗
║ CustomerGroup ║ Customer  ║ GroupCardKey ║
╠═══════════════╬═══════════╬══════════════╣
║ A             ║ Customer1 ║ ACustomer1   ║
║ A             ║ Customer2 ║ ACustomer2   ║
║ B             ║ Customer1 ║ BCustomer1   ║
║ B             ║ Customer3 ║ BCustomer3   ║
║ C             ║ Customer4 ║ CCustomer4   ║
║ C             ║ Customer5 ║ CCustomer5   ║
╚═══════════════╩═══════════╩══════════════╝

FactSale Table

╔═════════╦══════════════╦════════╗
║ Invoice ║ GroupCardKey ║ Amount ║
╠═════════╬══════════════╬════════╣
║       1 ║ ACustomer1   ║    100 ║
║       2 ║ BCustomer3   ║    200 ║
╚═════════╩══════════════╩════════╝

I write a "BoughtSomething?" measure and the result looks like

╔══════════════╦═════════════════╗
║ GroupCardKey ║ BoughtSomething ║
╠══════════════╬═════════════════╣
║ ACustomer1   ║ YES             ║
║ ACustomer2   ║ NO              ║
║ BCustomer1   ║ NO              ║
║ BCustomer3   ║ YES             ║
║ CCustomer4   ║ NO              ║
║ CCustomer5   ║ NO              ║
╚══════════════╩═════════════════╝

But I don't want to see no effective customer group at all. I tried but failed. Please help me to get the report like this one.

╔══════════════╦═════════════════╗
║ GroupCardKey ║ BoughtSomething ║
╠══════════════╬═════════════════╣
║ ACustomer1   ║ YES             ║
║ ACustomer2   ║ NO              ║
║ BCustomer1   ║ NO              ║
║ BCustomer3   ║ YES             ║
╚══════════════╩═════════════════╝
Brock Adams
  • 90,639
  • 22
  • 233
  • 295
Phyo Min Yu
  • 153
  • 1
  • 10
  • 1
    Show us what you wrote to get your current result – Rob Feb 02 '16 at 05:08
  • Define how a customer group is considered "no effective" – GregGalloway Feb 02 '16 at 12:53
  • @GregGalloway if a GroupCardKey under a CustomerGroup is found in FactSale table, that CustomerGroup is sales effective. – Phyo Min Yu Feb 02 '16 at 13:41
  • @PhyoMinYu what's the difference between BoughtSomething and "sales effective"? I don't understand why your last table is showing more than the two customer groups who bought something. – GregGalloway Feb 02 '16 at 14:10
  • @GregGalloway only two groups. A and B. in short, if all Customer in a CustomerGroup have "NO" value for "boughtsomething" measure, i don't want to show the whole customergroup in the analysis report. But still want to analysis all customers of remaining customergroups. either YES value or NO value. – Phyo Min Yu Feb 02 '16 at 14:37

1 Answers1

1

Try this. Basically check if the customer group bought something in the current date filter context and if so check the individual customer:

Bought Something:=IF(
 CALCULATE(COUNTROWS(FactSale), ALLEXCEPT(DimGroupCustomer[CustomerGroup])) >0,
  IF(COUNTROWS(FactSale)>0,"YES",NO")
)
GregGalloway
  • 11,355
  • 3
  • 16
  • 47
  • it works like magic. but i don't fully understand how this works. CALCULATE(COUNTROWS(FactSale), ALLEXCEPT(DimGroupCustomer[CustomerGroup]) could you explain little bit more? – Phyo Min Yu Feb 03 '16 at 02:39
  • 1
    @PhyoMinYu ALLEXCEPT clears the context on everything in that table except for CustomerGroup. So it lets you see the total for the whole group. That help? – GregGalloway Feb 03 '16 at 03:29