Forum Discussion
Solved
Assigning a column value based on other column
I have data something like this: Catagory value final value A 1 1 A 2 1 B 2 2 B 3 2 B 4 2 Basically number 1,2,3,4 are priority and if any atagory has prio 1 also 2...
It sounds like you are just trying to get the min of each group /category is that right?
You'll need an expression like
GroupMin = calculate(min('Table'[Value]), ALLEXCEPT('Table','Table'[Cat]))Calculate will give you the min, the first parameter calculation, and the context is specified in the second paramter, AllExcept. In this case the context "all except" tells DAX to return all values in the table, ie, ignore all groupings per row, except the cat grouping.This gives:Pi
The solution then is something like this:
Extended Logic =var contains5= calculate(CONTAINS('Table','Table'[Value],5),ALLEXCEPT('Table','Table'[Category]))var contains6=calculate(CONTAINS('Table','Table'[Value],6),ALLEXCEPT('Table','Table'[Category]))return Switch (FIRSTNONBLANK( 'Table'[Category],0) ,"A",if(contains6,FIRSTNONBLANK('Table'[Value],0),5),"B",if(contains5,FIRSTNONBLANK('Table'[Value],0),6),"C",if(and(contains5,contains6),floor(5+rand()+0.25,1)))vars contains5 and contains6 = these evaluate within the context of category, if the values 5 and 6 exist, respectively.
Switch Is similar to a case statement if you are familar with those - if you are not familiar think of it as an extended IF() statement where you can return multiple values depending on an input value.Here we are passing the category to the switch statement, and depending on which category it is, returning a different value.IF category is A, we look use contains 6 to determine what to return - either the value, or 5. Similar to B.for C we are checking both contains5 and contains6 and if that's the case, using rand() to assign 5's and 6'sHTHPi
DAX would be good for me, I dont want to go on Mquery for this