Forum Discussion
Relationship based on the multiple date columns
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.
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.
Thanks for your Replay,
Yes, in my Fact table i have a Cust_ID which is made a join with Customer Type DIm table.
and Logic is whenever customer has created a account one Cust ID will be generated which is associated with create date. and when the same customer activated his account then the same Cust ID will be associated with active date column. So here activated Customer has 2 dates , one is created date and another one is Activated date, if customer is not activated he should has only created date.
The Customer DIM table has only 2 columns which is Cust_ID and Cust_type.
Like Cust_ID 1 = activated
Cust_id 2= Enrollared
Cust_ID 3 = Null