Calculate Distinct Count from table using existing relationship

[npeterson]

I’m trying to pull a distinct count of an attribute from a related table. Instead of trying to mask my model for sharing purposes, I’ve replicated it using sample data in another database. Here is the formula that isn’t working correctly.

Subcategory Count =
CALCULATE(
DISTINCTCOUNT( ‘Product’[ProductSubcategoryKey] ),
USERELATIONSHIP( Dates[Date], Sales[Delivery Date] )
)

There is a relationship between Sales and Product and between Sales and Customers, but not a direct relationship between Product and Customers. This seems to be where it breaks down.

There are multiple date fields with inactive connections in the model, so I feel like I need to identify the date relationship that should be used. It is providing a distinct count, but when I try to apply this to a Table visual, it is not calculating correctly. In the visual below, there should only be counts if there is revenue and customer counts associated with the group.

How can I rewrite the DAX to deliver the desired results?
Basic Model.pbix (901.1 KB)

[Harsh]

Hello @npeterson,

Thank You for posting your query onto the Forum.

The concept/logic that you’ve applied for calculating the “Customer Count” that same logic will be applied here as well for calculating “SubCategory Count”.

Because as you’ve suggested that Product and Customer tables doesn’t have direct relationship between them but “Subcategory” table is dependant on the “Category” table and “Category” table is dependant on the “Product” table. And there’s a relationship between “Product” and “Sales” table based on the “ProductKey” between them because it’s like having “Category and SubCategory” within the “Product” table. So use “ProductKey” column from the “Sales” table as a link to filter out the results because you directly cannot use “ProductSubCategoryKey”. So formula will be like this -

Subcategory Count = 
CALCULATE(
    DISTINCTCOUNT( Sales[ProductKey] ),
    USERELATIONSHIP( Dates[Date], Sales[Delivery Date] )
)

Below is the screenshot of the results provided for the reference -

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Basic Model - Harsh.pbix (901.4 KB)

[npeterson]

Thank you for your response, @Harsh,

The result calculated with your modification provides a distinct count of the products, but still doesn’t provide the subcategories. As the model shows, there are only 44 possible subcategories, but over 2,500 products. So the maximum number that should appear in the Subcategory Count would be 44.

How can I establish a Distinct Count using the downstream relationship between Sales and Product Subcategory?

[BrianJ]

@npeterson ,

One thing I would strongly recommend is denormalizing your Products table to get rid of that snowflake structure and collapse it down to a standard star schema. It will inflate the size of the Products table substantially, but that’s fine - the Vertipaq engine handles that sort of duplication really efficiently, and I think it will make your DAX much easier, will automatically synch your slicers related to product dimensions, etc.

• Brian

[Harsh]

Hello @npeterson ,

Ok, so here’s the thing add “ProductSubCategoryKey” field into the “Sales” table which is not there.

I agree with @BrianJ here. While he was posting the message I was working onto the model. If you don’t combine them than atleast put all the keys into the Sales table so that when you want to perform this type of analysis relationship doesn’t become an issue here. So as I said now, I’ve added the “ProductSubCategoryKey” into the Fact table and have created the formula as provided below -

Subcategory Count - Using ProductSubCategory Key = 
CALCULATE(
    DISTINCTCOUNT( Sales[ProductSubcategoryKey] ),
    USERELATIONSHIP( Dates[Date], Sales[Delivery Date] )
)

And then to correct the grand totals write one simple small formula -

Subcategory Count - Using ProductSubCategory Key - Totals = 
SUMX(
    SUMMARIZE(
        Customer , 
        Customer[Education] , 
        "Totals" , 
        [Subcategory Count - Using ProductSubCategory Key] ) , 
    [Totals]
)

And now the results as per the “Delivery Date” is 40 and not 44. Below is the screenshot of the final results provided for the reference -

I’m also attaching the working of the Excel as well as PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

And lastly, as suggested by both of us please either merge the tables together and create one “Product” table or atleast provide all the keys into the “Sales” (Fact) table.

Thanks and Warm Regards,
Harsh

Dataset.xlsx (2.0 MB)

Basic Model - Harsh v2.pbix (1.0 MB)

[npeterson]

Thanks @BrianJ and @Harsh. As noted initially, the model I shared was a basic model and not the true model I’m working on for my company. Merging details into a single table would create an unwieldly model. I was able to use a CROSSFILTER on the original measure to arrive at the results.

Subcategory Count =
CALCULATE(
DISTINCTCOUNT( ‘Product’[ProductSubcategoryKey] ),
USERELATIONSHIP( Dates[Date], Sales[Delivery Date] ),
CROSSFILTER( ‘Product’[ProductKey],Sales[ProductKey],Both)
)