Dear Techies,
I need to create a dynamic measure ""No of Customer" that should work for all the fact tables in my tabular model. Let's say my model has two fact tables - FactTransactionActivity & FactInternetSales and three dimension tables - DimCustomer, DimGeography & DimDate. Both the fact tables are connected to all the three dimension tables.
The measure "No of Customer" that I'm gonna create should work if I want to see No of customer who have paid by CreditCard using "ModeofTransaction" column from FactTransactionActivity table in the slicer and also see No of customer who have purchased with Promotionalcode/discounts using "Discount Applied" from FactInternetSales table in a different report. I don't want to create two different measures to achieve the above scenario just one measure should do both the jobs.
I have written the DAX using SWITCH function as follows
=SWITCH (TRUE(), ISCROSSFILTERED(TrannsactionActivity[CustomerKey]), CALCULATE(DISTINCTCOUNT(Customer[CustomerKey]), Geography, TransactionActivity)
, ISCROSSFILTERED(InternetSales[CustomerKey]), CALCULATE(DISTINCTCOUNT(Customer[CustomerKey]), Geography, InternetSales )
, CALCULATE(DISTINCTCOUNT(Customer[CustomerKey]))--- Else part
)
The problem with the above calculation is, everytime I use Geography in my report it passes the first condition (ISCROSSFILTERED(TrannsactionActivity[CustomerKey])) and show results for TransactionActivity table always. I could not get the count for InternetSales table no matter what I use. I know it is the expected behaviour but how to re-write the above query in order to achieve what I'm expecting ?
Please help me with this
Thanks,
Latheesh
