Dax measure not showing correct values in table where Dim rows don't exist in Fact Table

[nilsvarmon]

I have two tables, DimUser and FactOrders. I would like to count the Nbr of Users which is a measure in my Dim table "Nbr of Users". It works fine in the first table even though the first User don't exist in the Fact table. However, when I drag in a column from the Fact table (Location- kind of the same as Location from DimUser) the first User loses the vlaue 1, but the Total still display 3. Why doesn't it show 1 with a BLANK in "Orders" and "Location (From Fact)"?

[v-yiruan-msft]

Hi @nilsvarmon ,

Are the table DimUser and "Nbr of Users" same table? Or they are totally different? Could you please share the relationship among these tables(DimUser, "Nbr of Users" and FactOrders) in order to provide you a suitable solution? Also provide the forumla of measure [Nbr of Users]. Thank you.

Best Regards

[nilsvarmon]

Hi!

Here are the definitions of the measures:

Nbr of Users = DISTINCTCOUNT('DimUsers'[UserID])

Nbr of Orders = DISTINCTCOUNT('FactOrders'[OrderID])

Here is the relationship:

In the end result I would like to show that "Malmö" also has 1 user attached to it, but with no corresponding rows in FactOrders:

It's when I use the column "Location" from FactOrders that the problem occurs.

[v-yiruan-msft]

Hi @nilsvarmon ,

I created a sample pbix (see the attachment) for you, please check whether that is what you want.

1. Set the relationship direction as single between table DimUsers and FactOrders

2. Create another new measure to replace measure [Nbr of Users]

Measure = SUMX(VALUES('DimUsers'[Location]),[Nbr of Users])

Best Regards

[nilsvarmon]

Hi!

This solution partly works, thank you! The measure with SUMX seems to work, but how come I see "Goteborg" the in Location column to the right. UserID=4630163 has no rows in the Fact, so it should be Blank in that cell, right?

And when I slice on that User, it looks strange, it shows twice which it shouldn't do.

[v-yiruan-msft · Accepted solution]

Hi @nilsvarmon ,

I updated the sample pbix file(see attachment), please check whether that is what you want.

Best Regards