Forum Discussion
Relationship based on the multiple date columns
Hi ,
I have a date Dim and one customer fact table. And another Dim table called customer type.
In the Customer fact table I have a two different date columns. Let’s say 1) customer create date 2 ) customer active date.
Relationships as below.
- Customer Fact table joined with data table based on customer active date.
- Customer Fact table joined with Customer type table based on customer ID
I have created a new Measure called Count of Customer and added a slicer on Customer Type.
The customer type has 2 records like active and enrolled. When user select Customer type as an active the measure should be bring the count of customers based on the create date, and when user select customer type as a Enrolled the count of customer should be bring the count of customers based on the active date.
Here is the solution :
Duplicated the table and made the relationship with date dim table.
And in the dax function used below formula.
IF (
SELECTEDVALUE ( customer_type[cust-id] ) = 11
&& SELECTEDVALUE ( customer_type[cust-id]) = 10,
[Enrolledvalue],
[activevalues]
)
Enrolled values and active values are another dax function where I am calculating the KPI values.
9 Replies
Create both relationships as normal. The first one you create will be active, the second will be inactive and represented by a dotted line.
Then you use the USERELATIONSHIP() function to activate in a measure. So a measure using the inactive relationship might look like this:
= CALCULATE ( SUM ( Sales[SalesDollars] ), USERELATIONSHIP ( Dates[Date], Sales[ShipDate] ) )Whereas the active relationship wouldn't need to be activated, it is on by default, so a normal SUM(Sales[SalesDollars]) would work for the billing date as an example.
I tied it but unfortunately it’s not working when I change the slicer selections.
Excepting result as below.
Cust_ID
Customer Name
create date
Active date
1
Debra
1/3/2019
2
Kasha
10/20/2017
11/12/2017
3
Tameka
5/12/2018
5/15/2018
4
Charolette
8/30/2018
9/2/2018
5
Lyndsey
1/3/2019
6
Pamelia
1/3/2019
7
Jacqulie
1/3/2019
Lets say User selected Customer slicer as an Active
Then the result should be 3
And if user selected Customer Slicer as a Enrolled
Then the Result should be 7
Well, this is kind of janky, but you didn't explain how you were determining how it was active or not, and I don't see the relevance to the two dates and being related to the date table.
See my attached file. Basically, I did this:
- In Power Query, I created a new column to add the status - Active or Enrolled, based on some logic of what is in the two date columns. This gives me a slicer field to work with. You didn't say how you got your slicer that I saw. You say it was based on the Customer Type table, but that just has their ID and name/description.
- In DAX, I created a measure that if it is active, count the filtered records. If it is Enrolled, count everything since they aren't in your table if they aren't enrolled.
Record Count = IF( ALLSELECTED('Customer Info'[Is Active])="Active", COUNTROWS('Customer Info'), COUNTROWS(ALL('Customer Info') ) )
If that doesn't work you are going to have to provide a model to look at. I'm making too many guesses to figure out how your data is laid out and where your slicer is coming from.
