Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
latheesh89
Helper II
Helper II

Create dynamic measure that work across multiple facts

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

 

 

 

1 REPLY 1
v-yulgu-msft
Employee
Employee

Hi @latheesh89,

 

Please provide some dummy data and show your expected output.

How to Get Your Question Answered Quickly

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.