Forum Discussion
frankhofmans
4 years agoHelper IV
Track change in table
hi PBI experts, i have the following question: I have a contract database (with > 50 colums en > 100.000 rows). Some contracts have changes in within a year: Contract year Date start ...
- 4 years ago
Hi,
I am not sure how your data model looks like or how your desired outcome visualization looks like, but please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below, and I hope the below can give an idea on how to create and apply the measures to your data model.
Selling price status: = VAR currentyear = MAX ( 'Year'[Contract year] ) VAR sellingpricetable = SUMMARIZE ( RELATEDTABLE ( Data ), Data[Selling price], Data[Date start] ) VAR countsellingprice = COUNTROWS ( sellingpricetable ) RETURN IF ( HASONEVALUE ( Contract_ID[Contract ID] ), IF ( countsellingprice = 1, "NoChange / " & MAXX ( sellingpricetable, Data[Selling price] ), CONCATENATEX ( sellingpricetable, Data[Selling price] & " " & Data[Date start], "->" ) ) )Payment term status: = VAR currentyear = MAX ( 'Year'[Contract year] ) VAR sellingpricetable = SUMMARIZE ( RELATEDTABLE ( Data ), Data[Payment term], Data[Date start] ) VAR countsellingprice = COUNTROWS ( sellingpricetable ) RETURN IF ( HASONEVALUE ( Contract_ID[Contract ID] ), IF ( countsellingprice = 1, "NoChange / " & MAXX ( sellingpricetable, Data[Payment term] ), CONCATENATEX ( sellingpricetable, Data[Payment term] & " " & Data[Date start], "->" ) ) )
Jihwan_Kim
4 years agoSuper User
Hi,
I am not sure how your data model looks like or how your desired outcome visualization looks like, but please check the below picture and the attached pbix file.
I tried to create a sample pbix file like below, and I hope the below can give an idea on how to create and apply the measures to your data model.
Selling price status: =
VAR currentyear =
MAX ( 'Year'[Contract year] )
VAR sellingpricetable =
SUMMARIZE ( RELATEDTABLE ( Data ), Data[Selling price], Data[Date start] )
VAR countsellingprice =
COUNTROWS ( sellingpricetable )
RETURN
IF (
HASONEVALUE ( Contract_ID[Contract ID] ),
IF (
countsellingprice = 1,
"NoChange / " & MAXX ( sellingpricetable, Data[Selling price] ),
CONCATENATEX (
sellingpricetable,
Data[Selling price] & " " & Data[Date start],
"->"
)
)
)
Payment term status: =
VAR currentyear =
MAX ( 'Year'[Contract year] )
VAR sellingpricetable =
SUMMARIZE ( RELATEDTABLE ( Data ), Data[Payment term], Data[Date start] )
VAR countsellingprice =
COUNTROWS ( sellingpricetable )
RETURN
IF (
HASONEVALUE ( Contract_ID[Contract ID] ),
IF (
countsellingprice = 1,
"NoChange / " & MAXX ( sellingpricetable, Data[Payment term] ),
CONCATENATEX (
sellingpricetable,
Data[Payment term] & " " & Data[Date start],
"->"
)
)
)