Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Shifting values from multiple rows to one row for Unique ID...

I'm using Power BI connecting to Salesforce to access the Opportunity Field History object (table).  I'd like to end up with each stage an opportunity hits, in order, on a single row so I can then bu...
Need Help
v-eachen-msft's avatar
v-eachen-msft
Icon for Community Support rankCommunity Support
6 years ago

Hi Anonymous ,

 

You could refer to the following DAX:

Column =
RANKX (
    FILTER ( 'Table', 'Table'[OppID] = EARLIER ( 'Table'[OppID] ) ),
    'Table'[Date],
    ,
    ASC,
    DENSE
)
Table 2 =
SUMMARIZE (
    'Table',
    "ID", DISTINCT ( 'Table'[OppID] ),
    "Stage 1", CALCULATE ( SELECTEDVALUE ( 'Table'[OldValue] ), 'Table'[Column] = 1 ),
    "Stage 2", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 1 ),
    "Stage 3", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 2 ),
    "Stage 4", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 3 ),
    "Stage 5", CALCULATE ( SELECTEDVALUE ( 'Table'[NewValue] ), 'Table'[Column] = 4 )
)

Here is my test result.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    v-eachen-msft  I was super excited for this, but it threw an error for me when attmpting to execute the table:

     

    "A table of multiple values was supplied where a single value was expected."

     

    Any thoughts?

    • v-eachen-msft's avatar
      v-eachen-msft
      Icon for Community Support rankCommunity Support
      6 years ago

      Hi Anonymous ,

       

      Do you have multiple OppIDs?