cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
cnicholls Frequent Visitor
Frequent Visitor

Display Fields from Active and Inactive Rows in Type 2 Data Table Using Table Visualisation

Hi,

 

Context

I have a Type 2 slowly changing dimension table that is listing out user names, id and email also with  "RecordActiveFrom" and "RecordActiveTo". The entry with a blank "RecordActiveTo" is the most recent record.

 

Requirement

In my report I need to show a table of users along with when they first used the system.  Due to limitations with the system that authors the data we are not able to store a 'UserCreatedOn' column in the database. Therefore, I need to show the most recent row for a given user alongside their earliest 'RecordActiveFrom' date which will represent when they appeared in our database. However, as yet I have not been able to achieve this result.

 

The table in my report for example should look like:

 

User ID First Name Last name Record Active From

1JillSmith27 January 2016

 

In my database, I would have:

 

ID FirstName LastName RecordActiveFrom RecordActiveTo

1JillSmith01 February 2016NULL
1JillJackson28 January 201629 January 2016
1JillJones27 January 201627 January 2016

 

I have tried a number of approaches with table filters, various DAX expressions with no success. 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Display Fields from Active and Inactive Rows in Type 2 Data Table Using Table Visualisation

Hi @cnicholls,

 

Create a calculated table with below DAX:

Type2_1 =
FILTER (
    ALLEXCEPT ( 'Type 2', 'Type 2'[UserID] ),
    'Type 2'[RecordActiveFrom]
        = CALCULATE (
            MIN ( 'Type 2'[RecordActiveFrom] ),
            ALLEXCEPT ( 'Type 2', 'Type 2'[UserID] )
        )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Community Support Team
Community Support Team

Re: Display Fields from Active and Inactive Rows in Type 2 Data Table Using Table Visualisation

Hi @cnicholls,

 

Create a calculated table with below DAX:

Type2_1 =
FILTER (
    ALLEXCEPT ( 'Type 2', 'Type 2'[UserID] ),
    'Type 2'[RecordActiveFrom]
        = CALCULATE (
            MIN ( 'Type 2'[RecordActiveFrom] ),
            ALLEXCEPT ( 'Type 2', 'Type 2'[UserID] )
        )
)

1.PNG

 

Best regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.