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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Jeromec
Frequent Visitor

Odd Dimension Table Behavior

I am still relatively new to power bi, so i could very well be doing something wrong. I've broken my problem down to only include a couple of the dimension tables as this reproduces the behavior.   See screenshot below.

 

mode screenshot.png

 

I have a listings fact table and two dimension tables, one for locations and one for agents. A listing can live in multiple locations, that is a listing can be in a state and also in community or neighbourhood etc.. Similarly an agent can be involved in both sides of the transaction.  As such, I have a fact table in which there are multiple listings with the same listing_id, same agent_id but different locations id i.e. For simplicity I've excluded the fact that an agent can be on both sides. What problem is more basic as it relates to the location dimension.

 

Fact Table would contain (among other data) 

 

listing_id    listing_agent  Listing_location

x                 y                     1

x                 y                      2

etc...

 

filtered.png

 

The screenshot above shows the total number of listings, as I would expect, CV21122026 is repeated because it is in two locations. What's odd is that if I remove the name of the location from that table (name comes from location table), that table gets filtered and i only see one location not both? Yes, the count of listings in the card remains the same. I can't figure out why if I remove the location name from the table it filters and removes the "duplicate" listings, even though they aren't duplicated, in that they have the same listing_id but a different location?

 

Feels like I am asking an obvious question, but I wouldn't expect this behavior. If my only filter on the page is the agent, and the count is correct, why does it not show in the table?

 

Appreciate any insight.

 

 

2 REPLIES 2
v-zhangti
Community Support
Community Support

Hi, @Jeromec 

 

For the count of listing_id, you can try the DISTINCTCOUNT function.

DISTINCTCOUNT function (DAX) - DAX | Microsoft Docs

 

Best Regards,

Community Support Team _Charlotte

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Thanks for the reply. Yes, I know I can use disintct count. This doesn't solve my problem. Essentially

 

if I do this:

Count Listings for Current Period = CALCULATE(DISTINCTCOUNT(LISTINGS_FACT[LISTING_ID])) while there are 5 listings, the correct results is 3 because of the a listing in multiple locations, this is the expected result.
 
when I try to get a sumx with the distinct on listing_id, it always returns the rows with the duplicate location, it never seems to actually apply the distinct on listing_id.
 
Debug X = SUMX(LISTINGS_FACT,DISTINCT(LISTINGS_FACT[LISTING_ID]))   it would expect this to sum the 3 rows but instead it does the 5 rows.
 
 

 

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors