Get the latest record in an SCD2 for a unique ID, count unique IDS, within period set by date slicer
Hi all I have been struggling with this challenge latetly. My goal is to create a measure that count unique IDs that has the "Priority" of 1, within a set date period from the date slicer. The data comes from an SCD2 table that records entries of all unique contracts ("Applicants"). The one column that changes frequently is the column named "Priority". My goal is to, within the set date period from the date slicer, find the latest entry for all unique contracts and then do a unique count for all with "Priority" of 1. "Priority" can be filtered on the report page as well so I guess that filtering doesnt need to be done in DAX. The measure uses USERELATIONSHIP with "ApplicationDate". In plains SQL this can be solved with a ROW_NUM() with descending order, and then filter on the row number. I havent managed to use ROWNUMBER() in DAX in a dynamic way, having the date slicer dictating the ROWNUMBER() output and passing it forward in the measure. One way I could think of solving this is to lock in the count for each locked time period (say per week), but the users would like to see if it works with the date slicer. Sample from my table (Sorted by RecordEffectiveDate): MemberIDSource Priority ApplicationDate RecordEffectiveDate IsCurrent 454 1 2023-01-07T16:36:04.000+00:00 2023-01-10T00:00:00.000+00:00 FALSE 454 2 2023-01-07T16:36:04.000+00:00 2023-01-21T00:00:00.000+00:00 FALSE 789 1 2023-01-19T17:16:05.000+00:00 2023-01-21T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-03-08T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-03-08T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-03-09T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-03-09T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-03-10T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-03-16T00:00:00.000+00:00 FALSE 789 2 2023-01-19T17:16:05.000+00:00 2023-07-19T00:00:00.000+00:00 FALSE 789 3 2023-01-19T17:16:05.000+00:00 2023-07-24T00:00:00.000+00:00 TRUE 1011 1 2023-07-20T13:17:14.000+00:00 2023-07-24T00:00:00.000+00:00 FALSE 454 2 2023-01-07T16:36:04.000+00:00 2023-07-24T00:00:00.000+00:00 FALSE 1011 2 2023-07-20T13:17:14.000+00:00 2023-08-02T00:00:00.000+00:00 TRUE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-02T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-23T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-24T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-24T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-08-25T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-09-22T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2023-12-06T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2024-01-13T00:00:00.000+00:00 FALSE 454 1 2023-01-07T16:36:04.000+00:00 2024-01-31T00:00:00.000+00:00 TRUE Any help very much appreciated.
PATH-function for user/manager relation in combination with SCD2
Hello together, i have some example data like the following: scdID userID userIDsupervisor csdStartDate scdEndDate 1 1 2008-02-02 2 2 1 2009-03-04 2015-04-25 3 2 3 2015-04-25 4 3 2 2010-05-03 2014-02-01 5 3 1 2014-02-01 scdID is a unique identifier but because of the slowly changing dimension userID is not. I use a measure and date slider with a date table in background to filter the data to only see the data which is valid at a selected date. The measure is like: isInDateRange = IF ( MAX ( 'calendar'[Date] ) >= SELECTEDVALUE( 'dim_user'[csdStartDate] ) && ( MIN ( 'calendar'[Date] ) <= SELECTEDVALUE( 'dim_user'[scdEndDate] ) || ISBLANK( SELECTEDVALUE( 'dim_user'[scdEndDate] ) ) ) , 1, 0 ) I am filtering the visual with isInDateRange == 1. This works perfectly. But now I have the task to implement RowLevelSecurity so a manager can see al its childs but under consideration of the slowly changing dimension. So when I select a date I should only see data of people which where below me in hirachy at this cartain date. I would like to use the PATH function in this context but of course it does not work right away because userID is not unique. So i tried this as a measure: ManagerPath = CALCULATE( PATH( 'dim_user'[userID], 'dim_user'[userIDsupervisor]), FILTER('dim_user', [isInDateRange]==1) ) But this did not work. It results in the same error. Does someone has an idea how to solve this complex setting? Thank you very much for every hint! Jenesis
Filtering model on latest version of all attributes from all dimensions through DAX
Hello, I have a model that's comprised of multiple tables containing, for every ID, multiple rows with a valid_from and valid_to dates. This model has one table in that is linked to every other table (a table working as both a fact and a dimension). This fact has bi-directional cross filtering with the other tables. I also have a date dimension that is not linked to any other table. I want to be able to calculate the sum of a column in this table in the following way: - If a date range is selected, I want to get the sum of the latest value per ID from the fact able that is before the max selected date from the date dimension. - If no date is selected, I want to get the sum of the current version of the value per ID. This comes down to selecting the latest value per ID filtered on the dates. Because of the nature of the model (bi-directional with the fact/dimension table), I want to have the latest version of any attribute from any dimension selected in the visual. Here's an data example and the desired outcome: fact/dimension table: ID Valid_from Valid_to Amount SK_DIM1 SK_DIM2 1 01-01-2020 05-12-2021 50 1234 6787 1 05-13-2021 07-31-2021 100 1235 6787 1 08-01-2021 12-25-2021 100 1236 6787 1 12-26-2021 12-31-2021 200 1236 6787 1 01-01-2022 12-31-9999 200 1236 6788 Dimension 1: ID SK Valid_from Valid_to Name 1 1234 10-20-2019 06-01-2021 Name 1 1 1235 06-02-2021 07-31-2021 Name 2 1 1236 08-01-2021 12-31-9999 Name 3 Dimension 2: ID SK Valid_from Valid_to Name 1 6787 10-20-2019 12-31-2021 Name 1 1 6788 01-01-2022 12-31-9999 Name 2 My measure is supposed to do the following: - If no date is selected than the result will be a matrix like the following: Dim 1 Name Dim 2 Name Amount Measure Name 3 Name 2 200 - If July 2021 is selected than the result will be a matrix like the following: Dim 1 Name Dim 2 Name Amount Measure Name 2 Name 1 100 So the idea here is that the measure would filter the fact table on the latest valid value in the selected date range, and then the bi-directional relationships will filter the dimensions to get the corresponding version to that row with the max validity (last valid row) in the selected range date. I have tried to do the following two DAX codes but it's not working: Solution 1: Amount Measure= VAR _maxSelectedDate = MAX(Dates[Dates]) VAR _minSelectedDate = MIN(Dates[Dates]) VAR _maxValidFrom = CALCULATE( MAX(fact[valid_from]), DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate) || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate) ) RETURN CALCULATE( SUM(fact[Amount]), fact[valid_from] = _maxValidFrom ) Solution 2: Amount Measure= VAR _maxSelectedDate = MAX(Dates[Dates]) VAR _minSelectedDate = MIN(Dates[Dates]) VAR _maxValidFromPerID = SUMMARIZE( FILTER( fact, DATESBETWEEN(fact[valid_from], _minSelectedDate, _maxSelectedDate) || DATESBETWEEN(fact[valid_to], _minSelectedDate, _maxSelectedDate) ), fact[ID], "maxValidFrom", MAX(fact[valid_from]) ) RETURN CALCULATE( SUM(fact[Amount]), TREATAS( _maxValidFromPerID, fact[ID], fact[valid_from] ) ) Now the problem here is that with the first solution, filtering on other dimension work and I get the last version in the selected date range for all attributes of all used dimensions. But the problem here is that the max valid from is not calculated per ID, so I only get the max valid from overall. With the second solution, I do get the right max valid from per ID and the resulting number is correct, but for some reason, when I use other attributes from the dimensions, it duplicates the amount for every version of that attribute. So if somebody can explain this behaviour that will be great, and also, more importantly, if you have any solution to have both the latest value per ID and still keep filtering on other attributes, that would be great! Sorry for the long post, but I thought it's best to give all the details for a complete understanding of my issue, this has been picking my brain since few days now and I'm sure I'm missing something stupid but I turned to this community for help because I cannot seem to be able to find a solution! Thank you very much in advance for any help!
Count in SCD2 dimension - optimization
Hello PB community! Can anybody please help me with DAX optimization in this scenario: I have Tabular model with 10 dimension tables, no real facts. One of the user request is to count units in Units table through time. This table is a SCD2 table type and only 2 dates in table are valid_from and valid_to. They will use year-month and year attributes on reports and in slicers. Beside date attribute they wan't to use attributes Units Type and Country of Origin. I did't connect my Calendar table with Units table in Tabular model and I have combined this DAX code: Cnt of units:= CALCULATE ( // DISTINCTCOUNT ( 'Units'[unit_id] ), SUMX(VALUES('Units'[unit_id] ),1), FILTER ( 'Units', COUNTROWS ( FILTER ( VALUES ( 'CalendarTable'[Date] ), 'CalendarTable'[Date]>= 'Units'[unit_valid_from] && 'CalendarTable'[Date] <= 'Units'[unit_valid_to] ) ) ) ) So this code is doing some sort of cross join / cross aplly on date table and since Units table isn't really small (it has 500K+ rows) it's slow. Calendar table is from DB Model, not from PB and has data from 2015 to 2025. Current server timing from dax studio are like this: In grid: Year Month, Unit type, Country of origin and my metric Filter: Year in (2020, 2019) I already replaced DISTINCOUNT with SUMX code. With using DISTINCOUNT function: So, are there are ideas how to optimize this code even more? Is there any way I can solve this differently (connections in Tabular with Calendar table, etc..?) Similar problems: https://community.powerbi.com/t5/Desktop/How-to-find-number-of-customers-in-a-SCD2-type-table/m-p/97281
