Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more.
Get startedGrow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.
Hallo all,
i cannot find the way to do the following logic which is difference between rows, here is an example:
- the next Start date if a product in a certain machine is the same - for example for machine A firstdate (01.01) "apples", nextdate 16.02 "Apples" therefore here there is "no changeover"
- the next condition if product in next date is different than "changeover" for example, for Machine B, date: 20.08. "carrots" and Machine B, date: 05.10. "chocholates" therefore there was a "changever.
is there a way to calculate the difference between rows chronologically when we have several machines ?
thank you all alot for the help !
Miguel
Order | Start date | Machine | Product | Changeover ( to be calculated) |
123456 | 01.01.16 | Machine A | Apples | |
234567 | 16.02.16 | Machine A | Oranges | changeover |
345678 | 03.04.16 | Machine C | Pears | no changeover |
456789 | 19.05.18 | Machine B | Carrots | changeover |
567900 | 04.07.16 | Machine A | Oranges | no changeover |
679011 | 20.08.16 | Machine B | Carrots | no changeover |
790122 | 05.10.16 | Machine B | Chocolates | changeover |
901233 | 20.11.16 | Machine A | Oranges | no changeover |
123440 | 05.01.16 | Machine B | Chocolates | changeover |
112345 | 21.02.16 | Machine C | Pears | |
123456 | 08.04.16 | Machine C | Apples | changeover |
134567 | 24.05.16 | Machine B | Carrots | no changeover |
145678 | 09.07.16 | Machine A | Oranges | no changeover |
Solved! Go to Solution.
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:
Changeover =
VAR _predate =
CALCULATE (
MAX ( 'Table'[Start date] ),
FILTER (
'Table',
'Table'[Machine] = EARLIER ( 'Table'[Machine] )
&& 'Table'[Start date] < EARLIER ( 'Table'[Start date] )
)
)
VAR _preprod =
CALCULATE (
MAX ( 'Table'[Product] ),
FILTER (
'Table',
'Table'[Machine] = EARLIER ( 'Table'[Machine] )
&& 'Table'[Start date] = _predate
)
)
RETURN
IF (
ISBLANK ( _preprod ),
BLANK (),
IF ( _preprod = 'Table'[Product], "no changeover", "changeover" )
)
Or you can create a measure as suggested by @speedramps :
Measure =
VAR _seldate =
SELECTEDVALUE ( 'Table'[Start date] )
VAR _selmac =
SELECTEDVALUE ( 'Table'[Machine] )
VAR _selproduct =
SELECTEDVALUE ( 'Table'[Product] )
VAR _predate =
CALCULATE (
MAX ( 'Table'[Start date] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Machine] = _selmac
&& 'Table'[Start date] < _seldate
)
)
VAR _preprod =
CALCULATE (
MAX ( 'Table'[Product] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Machine] = _selmac
&& 'Table'[Start date] = _predate
)
)
RETURN
IF (
ISBLANK ( _preprod ),
BLANK (),
IF ( _preprod = _selproduct, "no changeover", "changeover" )
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi @Anonymous ,
I created a sample pbix file(see attachment) for you, please check whether that is what you want. You can create a calculated column as below:
Changeover =
VAR _predate =
CALCULATE (
MAX ( 'Table'[Start date] ),
FILTER (
'Table',
'Table'[Machine] = EARLIER ( 'Table'[Machine] )
&& 'Table'[Start date] < EARLIER ( 'Table'[Start date] )
)
)
VAR _preprod =
CALCULATE (
MAX ( 'Table'[Product] ),
FILTER (
'Table',
'Table'[Machine] = EARLIER ( 'Table'[Machine] )
&& 'Table'[Start date] = _predate
)
)
RETURN
IF (
ISBLANK ( _preprod ),
BLANK (),
IF ( _preprod = 'Table'[Product], "no changeover", "changeover" )
)
Or you can create a measure as suggested by @speedramps :
Measure =
VAR _seldate =
SELECTEDVALUE ( 'Table'[Start date] )
VAR _selmac =
SELECTEDVALUE ( 'Table'[Machine] )
VAR _selproduct =
SELECTEDVALUE ( 'Table'[Product] )
VAR _predate =
CALCULATE (
MAX ( 'Table'[Start date] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Machine] = _selmac
&& 'Table'[Start date] < _seldate
)
)
VAR _preprod =
CALCULATE (
MAX ( 'Table'[Product] ),
FILTER (
ALLSELECTED ( 'Table' ),
'Table'[Machine] = _selmac
&& 'Table'[Start date] = _predate
)
)
RETURN
IF (
ISBLANK ( _preprod ),
BLANK (),
IF ( _preprod = _selproduct, "no changeover", "changeover" )
)
If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.
How to upload PBI in Community
Best Regards
Hi Rena !
Thank you very much,
that worked great !
i have one small issue with the data, which is, when the Orders repeat, i have repeated data sets were Changeover and No changeover can appear double.
based on the Column results, can i make a measure that counts only "changeover" order"no changeover" from distinct orders ?
Thank you !
Miguel
Hi @Anonymous ,
You can create a measure as below to get it:
Measure =
CALCULATE (
DISTINCTCOUNT ( 'Table'[Order] ),
'Table'[Column] IN { "changeover", "no changeover" }
)
Best Regards
Hallo,
thank you for the answer, there is no "Select" in either Measure or Columns what function did you mean ?
kindest regards,
Miguel
Hi yukipilas
Please consinder this solution, clcik the thumbs up and accept as solution button. Thank you
Changeover =
VAR mydate= SELECT(table[startdate])
VAR myproduct = SELECT(table[product])
VAR prevdate=
CALCULATE(MAX(table[startdate]),
ALLEXCEPT(table[machine]),
table[startdate]) < mydate)
VAR prevproduct =
CALCULATE(SELECT(table[product],"Nonefound"),
ALLEXCEPT(table[machine]),
table[startdate]) = prevdate)
RETURN
SWITCH(TRUE(),
prevproduct = "Nonefound", BLANK(),
myproduct = prevproduct, "no changeover",
"changeover"
)
😀
i looked for a similar " Select" but it doesnt seem to work,
Hi again yukipilas
I am so sorry for my typing mistake, I did it on my mobile phone instead of the using the laptop. 😀
It should have been SELECTEDVALUE. 😀😀
I think should work provising that you Date column is a Date type field.
If it is a text field then the < logic may give undesired results
Please consinder this solution, clcik the thumbs up and accept as solution button. Thank you
Changeover =
VAR mydate= SELECTEDVALUE(table[startdate])
VAR myproduct = SELECTEDVALUE(table[product])
VAR prevdate=
CALCULATE(MAX(table[startdate]),
ALLEXCEPT(table[machine]),
table[startdate]) < mydate)
VAR prevproduct =
CALCULATE(SELECTEDVALUE(table[product],"Nonefound"),
ALLEXCEPT(table[machine]),
table[startdate]) = prevdate)
RETURN
SWITCH(TRUE(),
prevproduct = "Nonefound", BLANK(),
myproduct = prevproduct, "no changeover",
"changeover"
)
Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.
Ask questions in Eventhouse and KQL, Eventstream, and Reflex.
User | Count |
---|---|
84 | |
84 | |
65 | |
62 | |
55 |
User | Count |
---|---|
171 | |
110 | |
109 | |
73 | |
71 |