Forum Discussion

lalala932's avatar
lalala932
Frequent Visitor
4 years ago

Count occurrence based on condition

Hi

 

I have a table with multiple years, customers, and products (along with some other metrics such as country, age, etc). I would like to find out how many unique products each customer bought each year. I will then split this by country, age etc.

 

First, I don't know whether it's better to create a new table or measures? Probably measures?

 

I wanted to use this solution, but it only counts the number of occurences - I need a number of occurences of unique products for each unique customer, but then also filtered for each year.

 

Can someone please help? 

 

1 Reply

  • daXtreme's avatar
    daXtreme
    Icon for Solution Sage rankSolution Sage
    4 years ago

    Hi lalala932 

     

    Please create a proper star-schema model for this unless you want to face issues impossible to resolve in a one-table model (speaking from experience) later on.

     

    Here's some mandatory reading for all Power BI aspiring adepts: Understand star schema and the importance for Power BI - Power BI | Microsoft Docs

     

    Once you've got a proper model, then you can start writing DAX. Not the other way round. The measure will be something along these lines:

    // Say that you've got the following dimensions:
// Customers, Products, Dates (the date table in the model).
// You also have a fact table that these join to.
// Fact table should always have its all columns hidden,
// apart from degenerate dimensions (which you I believe
// don't have anyway).

[# Unique Prods Bought] = DISTINCTCOUNT( FactTable[ProductID] )

// This simple measure looks at the fact table
// (where there's a key to the Products dimension)
// and returns the number of distinct keys that are
// visible in the current context. If you start slicing
// and dicing your model by dimensions, it'll adjust
// automatically to the selections. So, if you, say,
// select a year from your Dates table and a customer
// from Customers, it'll return the number of distinct
// products bought by the customer in the very year.