Assigning a column value based on other column

[prat_daxer]

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.

[pi_eye · Accepted solution]

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

[prat_daxer]

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.

[prat_daxer]

There's as extended query to above problem. @pi_eye 

[pi_eye · Accepted solution]

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's

HTH 

Pi

[pi_eye · Accepted solution]

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

[prat_daxer]

DAX would be good for me, I dont want to go on Mquery for this

[pi_eye]

Hi prat_daxer,

Are you trying to do this with DAX (front end UI) or M query (data shaping and modelling)

Pi