calculated columns - Min value with GROUP BY in Power BI Desktop - 


id  datetime             new_column           datetime_rankx 1   12.01.2015 18:10:10  12.01.2015 18:10:10  1 2   03.12.2014 14:44:57  03.12.2014 14:44:57  1 2   21.11.2015 11:11:11  03.12.2014 14:44:57  2 3   01.01.2011 12:12:12  01.01.2011 12:12:12  1 3   02.02.2012 13:13:13  01.01.2011 12:12:12  2 3   03.03.2013 14:14:14  01.01.2011 12:12:12  3

i want make new column, have minimum datetime value each row in group id.

how in power bi desktop using dax query?

use expression:

newcolumn =   calculate(     min(       table[datetime]),       filter(table,table[id]=earlier(table[id])    )   )

in power bi using table data produce this:

enter image description here

update: explanation , earlier function usage.

basically, earlier function give access values of different row context.

when use calculate function creates row context of whole table, theoretically iterates on every table row. same happens when use filter function iterate on whole table , evaluate every row against filter condition.

so far have 2 row contexts, row context created calculate , row context created filter. note filter use earlier access calculate's row context. having said that, in our case every row in outer (calculate's row context) filter returns set of rows correspond current id in outer context.

if have programming background give sense. similar nested loop.

hope python code points main idea behind this:

outer_context = ['row1','row2','row3','row4'] inner_context = ['row1','row2','row3','row4']   outer_row in outer_context:     inner_row in inner_context:         if inner_row == outer_row: #this line filter , earlier              #calculate min datetime using filtered rows             ...             ...

update 2: adding ranking column.

to desired rank can use expression:

rankcolumn =    rankx(     calculatetable(table,allexcept(table,table[id]))     ,table[datetime]     ,hoja1[datetime]     ,1  )

this table rank column:

enter image description here

let me know if helps.


Comments

Post a Comment

Popular posts from this blog

jOOQ update returning clause with Oracle -

according http://www.jooq.org/doc/3.8/manual/sql-building/sql-statements/update-statement/ jooq doesn't support returning clause in update statements databases except firebird , postgres. does know, still correct oracle jooq? according oracle's documentation, oracle db supports returning clase delete, insert, or update statements. incorrect manual you're right - manual wrong , should fixed: https://github.com/jooq/jooq/issues/5470 single row update returning jooq 3.8 added support single row update .. returning statements. internally, uses callablestatement , pl/sql block of form: begin update "my_table" set "my_table"."column" = 'xyz' "my_table"."id" = 1 returning "my_table"."id", "my_table"."column" ?, ?; ? := sql%rowcount; end; the relevant issue is: https://github.com/jooq/jooq/issues/5190 multi row update returning a...
Read more

java - Warning equals/hashCode on @Data annotation lombok with inheritance -

i have entity inherits other. on other hand, i'm using lombok project reduce boilerplate code, put @data annotation. annotation @data inheritance produces next warning: generating equals/hashcode implementation without call superclass, though class not extend java.lang.object. if intentional, add '@equalsandhashcode(callsuper=false)' type. is advisable add annotation @equalsandhashcode (callsuper = true) or @equalsandhashcode (callsuper = false) ? if not added, 1 callsuper=false or callsuper=true ? the default value false . 1 if don't specify , ignore warning. yes, recommended add @equalsandhashcode annotation on @data annotated classes extend else object. cannot tell if need true or false , depends on class hierarchy, , need examined on case-by-case basis. however, project or package, can configure in lombok.config call super methods if not direct subclass of object. lombok.equalsandhashcode.callsuper = call for configuration system ...
Read more

java - BasicPathUsageException: Cannot join to attribute of basic type -

i using java 8 jpa (hibernate 5.2.1). have clause works perfectly, until introduce clause make use foreign key table values too. i getting following error: basicpathusageexception: cannot join attribute of basic type i think problem related fact have join table, , not sure how create join using join table. employee - employee_category - category model (employee.java): @manytomany(cascade = cascadetype.all, fetch=fetchtype.eager) @jointable ( name="employee_category", joincolumns={ @joincolumn(name="emp_id", referencedcolumnname="id") }, inversejoincolumns={ @joincolumn(name="cat_id", referencedcolumnname="id") } ) private set<category> categories; model (category.java): @id private string id; jpa when introduce following code, fails error above (i think problem new piece of code): join<t, category> category = from.join("id"); predicates.add(criteriabuilder.like(category....
Read more