Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a Calculated Column in which I want to show whether a 'Table.ContractId' became active. However, the dataset I work with sometimes shows for a certain 'Table.Date' the 'Table.ContractId' twice.
This dataset/Table I base my solution on, works the following way: every month it gives an overview of all contracts with their 'Table.ValidFrom' and 'Table.ValidTo' dates. However, as soon as there is a 'Table.ValidTo' WITHIN that same period, it will create another record with the 'Table.Date' set to that 'Table.ValidTo' within same period date.
Currently this is my measure 'CURRENT' in column 5 with a nested IF statement:
CURRENT =
IF(Table[ValidFrom].[Date] = Table[DATE].[Date] || (Table[ValidFrom].[Date] >= Table[DATE].[Date] && Table[ValidFrom].[Date] < DATEADD(Table[DATE].[Date];1;MONTH));
IF(Table[DATE].[Date] = Table[ValidTo].[Date];
IF(Table[ValidFrom] = Table[ValidTo];
IF(Table[ValidFrom].[Date] = Table[DATE].[Date] && PMCCONTRACTMUTATIONS[ValidTo].[Date] = Table[DATE].[Date];
0 --this is the Zero that in the first exact case should say 1 instead of 0 !
; 1); 0); 1); 0)
Here it checks whether 'Table.ValidFrom' is the same as 'Table.Date',
whether 'Table.ValidFrom' is within the period (month) of 'Table.Date',
whether 'Table.ValidTo' is equal to the 'Table.Date', WITHIN that same period, since that means there will be a duplicate value.
Though, the question is, how to recognize that duplicate value. Besides, this might not necessarily be the PREVIOUS RECORD, it might be ONE OF THE PREVIOUS RECORDS.
CONTRACTID | DATE | ValidFrom | ValidTo | CURRENT | EXPECTED |
HC012473 | 1-1-2015 00:00 | 14-9-2006 00:00 | 22-5-2013 00:00 | 0 | 0 |
HC025087 | 1-6-2015 00:00 | 25-6-2015 00:00 | 25-6-2015 00:00 | 1 | 1 |
HC025087 | 25-6-2015 00:00 | 25-6-2015 00:00 | 25-6-2015 00:00 | 0 | 0 |
HC076719 | 1-2-2017 00:00 | 1-2-2017 00:00 | 1-2-2017 00:00 | 0 | 0 |
HC076719 | 1-2-2017 00:00 | 1-2-2017 00:00 | 1-2-2017 00:00 | 0 | 1 |
HC074791 | 1-1-2018 00:00 | 1-1-2018 00:00 | 1-1-2018 00:00 | 0 | 0 |
HC074791 | 1-1-2018 00:00 | 1-1-2018 00:00 | 1-1-2018 00:00 | 0 | 1 |
HC079152 | 1-12-2018 00:00 | 1-12-2018 00:00 | 1-12-2018 00:00 | 0 | 0 |
HC079152 | 1-12-2018 00:00 | 1-12-2018 00:00 | 1-12-2018 00:00 | 0 | 1 |
Kind regards,
Igor
HI @Titatovenaar,
You can add an index column to your table and use the following calculated column formula to remark records based on index column:
CURRENT NEW =
VAR temp =
ADDCOLUMNS (
'Table',
"Duplicate", IF ( [DATE] = [ValidFrom] && [DATE] = [ValidTo], 0, 1 )
)
VAR dupCount =
COUNTX (
FILTER (
SUMMARIZE ( temp, [CONTRACTID], [Duplicate] ),
[CONTRACTID] = EARLIER ( 'Table'[CONTRACTID] )
),
[Duplicate]
)
VAR mRowCount =
COUNTROWS ( FILTER ( 'Table', [CONTRACTID] = EARLIER ( [CONTRACTID] ) ) )
VAR cRowCount =
COUNTROWS (
FILTER (
'Table',
[CONTRACTID] = EARLIER ( [CONTRACTID] )
&& [Index] <= EARLIER ( 'Table'[Index] )
)
)
RETURN
IF (
dupCount > 1,
IF ( [DATE] = [ValidFrom] && [DATE] = [ValidTo], 0, 1 ),
IF ( cRowCount = mRowCount && mRowCount > 1, 1, 0 )
)
Regards,
Xiaoxin Sheng
@Titatovenaar I don't know fully what your question is, but I think you might find the EARLIER function helpful here.
You can use EARLIER in combination with FILTER or other table function. What EARLIER does is enable you to compare one value in the current row context to the PREVIOUS ROW CONTEXT of the DAX expression, so when used correctly it can enable you to compare the current row to each and every other row in the table.
If you clarify your question we can try to provide you with a more specific answer, but research EARLIER if you haven't looked into it already.
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thx for the fast reply.
I will definitely check 'EARLIER' functionality right now.
What I simply want, if you forget about all the data set and the measure above is IF all 3 columns are the same, THEN a 0, UNLESS the exact same combination of values happened before.
ID, Col1, Col2, Col3, MEASURE
1, Y, X, X, 0
2, X, X, X, 1
3, X, Y, Y, 0
4, X, X, X, 0
Kind regards,
Igor
@Titatovenaar Thanks, that clarification helps a bit, but I'm still a little confused. Your sample table doesn't seem to match what you wrote in the paragraph?? If columns don't match it should also be 0?
Also, does contract number matter? In your sample table it seems like it does not, but I'm assuming it should from looking at your original post?
I would recommend creating a rank (similar to @v-shex-msft 's advice to create Index column), to help order the lines by date:
Copying DAX from this post? Click here for a hack to quickly replace it with your own table names
Has this post solved your problem? Please Accept as Solution so that others can find it quickly and to let the community know your problem has been solved.
If you found this post helpful, please give Kudos C
I work as a Microsoft trainer and consultant, specialising in Power BI and Power Query.
www.excelwithallison.com
Thanks for the extensive replies!
Xiaoxin Sheng's solution so far I couldn't try, because I cannot make a unique row index based on my duplicate string/date fields in my table. Or is there a workaround for that? I only see measures doing that row_count trick, not in a calculated column.
AllisonKennedy, your comment brought me closer to the solution: I now flagged the duplicates with the EARLIER functionality. However, I want only the first instances of the duplicates to get a certain value. So far this is my dataset, in which the column Duplicate uses COUNTROWS functionality to count the duplicates.
ContractId | Date | ValidFrom | ValidTo | Duplicate | FirstInstance |
1 | 1-1-2015 | 1-1-2006 | 22-5-2013 | 1 | 0 |
2 | 1-6-2015 | 25-6-2015 | 25-6-2015 | 1 | 0 |
2 | 25-6-2015 | 25-6-2015 | 25-6-2015 | 1 | 0 |
3 | 1-2-2017 | 1-2-2017 | 1-2-2017 | 2 | 1 |
3 | 1-2-2017 | 1-2-2017 | 1-2-2017 | 2 | 0 |
4 | 1-1-2018 | 1-1-2018 | 1-1-2018 | 2 | 1 |
4 | 1-1-2018 | 1-1-2018 | 1-1-2018 | 2 | 0 |
I can then later add this FirstInstance to my previously mentioned IF statement, since it did not include those first instances yet! Maybe not the most beautiful, but it does the trick!
Kind regards,
Igor
HI @Anonymous,
You can refer to the following link to add an index column to your table in quey editor side:
Power Query Helps To Create Index Column
Regards,
Xiaoxin Sheng
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
117 | |
104 | |
77 | |
73 | |
50 |
User | Count |
---|---|
145 | |
109 | |
108 | |
90 | |
64 |