Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello Everyone,
I have an SCD2 type table like below.
I have a date table and time table as well. I want to find out the number of distinct customers when the user selects a date or show number of users by date so on.. Does this model help me do that? It's a typical SCD Type 2 dimension I am using for my Data.
Thanks for your help in advance.
Solved! Go to Solution.
Hi @a4apple,
I believe above formula has done what you describe about event or membership type if there is no mismatch with my understand.
Just re-check 2 things:
You just drag&drop Slicer control for event/membership type to check what we are doing.
Firstly, I create Dates table for your date filters:
Modeling Tabl -> New Table ->
Dates = CALENDARAUTO()
In your data table, I create 2 Calculated Measure:
Selected = FIRSTNONBLANK(values(Dates[Date]),1)
(To get current selected value)
And Distinct count customer by CustomerID
No. of cus = CALCULATE(DISTINCTCOUNT(Sheet1[CustomerID]),filter(Sheet1,Sheet1[StartDate]<= [Selected] && [Selected] < Sheet1[EndDate] ))
If this works for you please accept it as solution and also give KUDOS.
@CheenuSing, @tringuyenminh92 guys thank you so much. Most of the work is done with your dax statement. I want to know, what if I want to filter the data using an other table as well along with date? How can I acheieve that?
For example, lets say there are events table, I want to know how many customers are with eventtype = 'A' on that day?
like filter from another dimension other than date as well and not Facts when it has the CustomerID but not the surrogate Key (CustomerKey).
I hope my question is understandable.
or else,
Lets say I have a dimension which is also SCD 2 about customers and their membership types.
If I select Membership Type = 'Platinum' and Date = '2016-11-25', I want to know the number of customers with membership type "Platinum" on the day of "2016/11/25". Is this possible by two dimension tables? or do we need them as Facts?
Please help.
Hi @a4apple,
I believe above formula has done what you describe about event or membership type if there is no mismatch with my understand.
Just re-check 2 things:
You just drag&drop Slicer control for event/membership type to check what we are doing.
Hi @a4apple
Create measure as follows
ActiveCustomers = Calculate(
DISTINCTCOUNT(CustomerSCD[CustomerId]),
FILTER( CustomerSCD,
COUNTROWS( FILTER(VALUES('Calendar'[Date]),
CustomerSCD[StartDate] <= 'Calendar'[Date] &&
CustomerSCD[EndDate] >= 'Calendar'[Date] ) )
> 0 )
)
This measure does the following:
Calculate the distinct count of CustomerSCD[CustomerId] for those rows in the CustomerSCD table that has more than 0 rows in the Calendar table where CustomerSCD[StartDate] <= 'Calendar'[Date] and CustomerSCD[EndDate] >= 'Calendar'[Date].
If this works for you please accept it as solution and also give KUDOS.
Cheers
CheenuSing
User | Count |
---|---|
90 | |
74 | |
67 | |
63 | |
55 |
User | Count |
---|---|
99 | |
88 | |
72 | |
60 | |
59 |