CODEWITHSUNDEEP
powerbidax
Shahab Haidar
Shahab Haidar

Reputation: 641

Count distinct in Power BI

I have a table in which customer information is saved.

enter image description here

My boss has asked me to create a drill down report where in he wants all active customer count.

1st condition8

Suppose a customer is joined in year 2017 then for every year after 2017 the count should be as 1.

2nd condition

Suppose a customer is joined in month 2018-Feb then for every month after 2018-Feb the count should be as 1.

Same with "Day" drilldown as well.

I created a measure where in I have written below code but that didn't work.

I have also created a calculated column for year and created a non active relationship between both tables.

Customer Count = 
   CALCULATE(
   DISTINCTCOUNT('Customer Status'[CustomerID]),
   USERELATIONSHIP('Time'[Year],'Customer Status'[Customer Status Year])
)

This is working for year but for month it is showing same count for all month.

Upvotes: 0

Views: 278

Answers (1)

OBro
OBro

Reputation: 11

You can create a relationship that uses both Year and Month together. Then you can USERELATIONSHIP that field instead of the current selection of just Year - this should allow active customer count to calculate by the month as well.

For creating the relationship, there may be a more lucrative solution but you can just CONCATENATE Year/Month together.

Upvotes: 1

Related Questions