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.
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
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.
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