Relationship based on the multiple date columns

[Anonymous]

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.

[edhans]

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.

[Anonymous]

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

[edhans]

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. 

[Anonymous]

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  

[edhans]

Ok. Like I said, I'd want to see your model, or a model with fake data. I cannot see if your relationships are bi-directional or not, and that comes into play more when you have an non-star schema, which it seems you do as you have DIM tables off of DIM tables, which isn't recommended for query analysis. Good for update/add/delete in a database, not for query performance in Power BI.

But I cannot keep guessing and adding new tidbits of info with what you've given so far to go futher. Perhaps someone else can.

[Anonymous]

 Here am sending the data model and data per table ,Kindly find the attached file .    Its really apprieciate for your positive respones and your help.  Data Model Power BI Sample DataData ModelSample DataPower BI report

[Anonymous]

Any inputs please 

[Anonymous]

Any one please suggest on this request .

[Anonymous · Accepted solution]

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.