Creating a column so that it can be used both in a matrix and an Append operation in power query

[Anonymous]

I have the following architecture set up in Power BI.

The majority of the data is numbers.

The source for the numbers in table 1 is an SQL query - uncomplicated, happy days.

The source for the numbers in table 2 is more complicated and the values need to be calculated once the data has been brought in from the source.  In order for the values to be available for the append operation, I've done these as custom columns via power query.  I also have a set of values done with calculated columns (before I realised I'd need custom columns).

The append is working fine and all the data in table 3 (the table with the append of table 1 and table 2) is as it should be.  

The issue is when I try and create a matrix with data in table 3 it's only using the first row of data.  I'm getting this... (and the telltale sign of the 'first' when I pull the field onto the values pane of the visual).

When I should be getting this (this is created using the set of calculated columns on table 2)

Is there any way to get the matrix to work using the data in table 3?

An obvious alternative is to just use table 2, using the calculated columns, but that means I've got to maintain the calculation method in two places, power query and DAX (and for each calculated column as I can't figure out a way to write and call functions using DAX).

Any help greatfully appreciated.

[Anonymous]

Hi @Anonymous ,

Did @audreygerred  response solve your problem? If your problem is solved, please mark helpful replies as solutions, if not please point them out.

Best Regards,

Neeko Tang

[audreygerred · Accepted solution]

Check your data type on the field that is pulling over as 'First', that usually is a sign it is a text field. 

[Anonymous]

@audreygerred - thanks for tht - you're bang on it's text.  I'll figure out how to change them to number in the function.  I'll let you know how it goes - it might be a few days until I get to work on it again.

Thanks again - I'm always so grateful for people helping out - I learn so much from posts that have already been raised and answered, and my own always get answererd, usually with some fairly basic solution but all you need is another pair of eyes!!