Forum Discussion
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 assign both to 1.
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
6 Replies
DAX would be good for me, I dont want to go on Mquery for this
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
Catagory value final value A 5 5 A 7 5 B 7 6 B 6 6 C 5 5 C 6 5 C 5 5 C 6 6 Condition 1: Cat A --> Assign prio 5 only if 6 is not there against A.
Condition 2: Cat B --> Assign prio 6 only if 5 is not present against B.
Condition 3: Cat C --> If prio 5 and 6 both present against C, assign random 75% 5's to cat C and in rest 25%, 6's.
There's as extended query to above problem. pi_eye
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
Hi prat_daxer,
Are you trying to do this with DAX (front end UI) or M query (data shaping and modelling)
Pi
