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

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
Karl-D
Helper I
Helper I

DAX Measure to Summarize Ship-To State when there are 2 Order Fact Tables

I have the following tables and relationships:

  • fact_orders_a = orders from one system
  • fact_orders_b = orders from another system
  • dim_customer = 1:* to each fact on dim_customer_key
  • dim_item = 1:* to each fact on dim_item_key

I have a matrix visual showing:

  • Customer Name = dim_customer.customer_name
  • Item Name = dim_item.item_name
  • Order Measure = distinctcount(fact_orders_a.order_number) + distinctcount(fact_orders_b.order_number)
  • Qty Measure = sum(fact_orders_a.order_qty) + distinctcount(fact_orders_b.order_qty)

If I try to add the "Ship-To State" field from either fact table to this visual, it shows all "Ship-To-States" since the fact tables aren't directly related.

How can I use DAX to create a measure that shows the Country and State from the applicable fact_orders table records on the rows?

3 REPLIES 3
Karl-D
Helper I
Helper I

To illustrate, the point of the matrix on the right is to combine the two on the left.
You'll see that the totals are correct.

I'm just trying to figure out how I can avoid the cartesian product where every ship-to-state from each of the two fact tables is shown rather than just those that are involved in the totals.

 

KarlD_0-1715955393442.png

In this example, the "total bottles" measure is calculated as follows

= 
    CALCULATE(
        DISTINCTCOUNT('fact_orders_a'[Order No.])
        ,'fact_orders_a'[Customer Type]="Wholesale"
        ,'fact_orders_a'[Is Refunded Order]=FALSE())
   -
    CALCULATE(
        DISTINCTCOUNT('fact_orders_a'[Order No.])
        ,'fact_orders_a'[Customer Type]="Wholesale"
        ,'fact_orders_a'[Is Refunded Order]=TRUE())
    +
    CALCULATE(
        DISTINCTCOUNT('fact_orders_b'[order_no])
        )
 

 

Karl-D
Helper I
Helper I

Hi @Jihwan_Kim.  I understand SWITCH function, and it may come into play here later.  Thank you for your suggestion.

However, the main issue with my model is that, since the two order tables are not directly related, attempting to show a ship-to-state in the rows section of the matrix works for rows where the values are from that table, but for rows where the values are from the other table, it shows all ship-to-states.

Jihwan_Kim
Super User
Super User

Hi,

I am not sure how your semantic model looks like, but one of ways to achieve this is to use SWITCH DAX function in the measure with your business rules when to use which fact table.

SWITCH function (DAX) - DAX | Microsoft Learn

 

 

If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


Visit my LinkedIn page by clicking here.


Schedule a meeting with me to discuss further by clicking here.

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

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