Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Conditional column for distinct values of another column

Hi everyone,

 

I have a table like the one below. 

CustomerQuoteHoursDesiredOutcome
Apple123456501
Apple123465500
Apple1234561001
Microsoft9876542001
Microsoft9876542000
Microsoft456321501

I am trying to create the DesiredOutcome column so I can then create a measure that sums up all the hours of each distinct quote number. Is there a way to create a column like that or another way to get to the measure value I eventually want? I am not sure how to go about this exactly. The measure value would return 400 once everything was all working.

 

Any guidance would be super helpful! Thank you!

Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    Hi Anonymous 

    Due to the duplicate value in your table ,you need to create an index column to distinguish them .Add an index column in Query Editor and then return to Desktop view .

    Then create a calculated column to return the next hour in turn .

    hour 2 =
var _index='Table'[Index]+1
return
CALCULATE(SELECTEDVALUE('Table'[Hours]),FILTER(ALLEXCEPT('Table','Table'[Customer]),'Table'[Index]=_index))

    Create a measure to compare the two hour columns .

    desire result = SWITCH(TRUE(),SELECTEDVALUE('Table'[Hours])=SELECTEDVALUE('Table'[hour 2]) ,1,SELECTEDVALUE('Table'[hour 2])=0,1,0)

    The final result is as shown :

    I have attached my pbix file ,you can refer to it .

     

    Best Regards

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1 Reply

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    Hi Anonymous 

    Due to the duplicate value in your table ,you need to create an index column to distinguish them .Add an index column in Query Editor and then return to Desktop view .

    Then create a calculated column to return the next hour in turn .

    hour 2 =
var _index='Table'[Index]+1
return
CALCULATE(SELECTEDVALUE('Table'[Hours]),FILTER(ALLEXCEPT('Table','Table'[Customer]),'Table'[Index]=_index))

    Create a measure to compare the two hour columns .

    desire result = SWITCH(TRUE(),SELECTEDVALUE('Table'[Hours])=SELECTEDVALUE('Table'[hour 2]) ,1,SELECTEDVALUE('Table'[hour 2])=0,1,0)

    The final result is as shown :

    I have attached my pbix file ,you can refer to it .

     

    Best Regards

    Community Support Team _ Ailsa Tao

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.