Showing results for 
Search instead for 
Did you mean: 
latheesh89 Helper I
Helper I

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







Microsoft v-yulgu-msft

Re: Create dynamic measure that work across multiple facts

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

New Ranks Launched March 24th!

New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

‘Better Together’ Contest Finalists Announced!

‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

Arun 'Triple A' Event Video, Q&A, and Slides

Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

Join THE global Microsoft Power Platform event series.

Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors