Merge Data created in Measures

[TimK]

I have what i find is a challenging issue, which i will explain:

I have a number of Measures, which basically sumarise 2 different sets of data, but the tags come from the same source but different levels.

I can sumarise the Measures as follows:

Measure 1: it basically can create a table such as this:

Project	Value
AA	125
BB	225
CC	124
DD	253

Measure 2: it has a similar structure, but expands by one level [DD]:

Project	DD	Value
AA	L	125
AA	M	125
BB	L
BB	M	100
CC	L	120
CC	M
DD	L
DD	M

So what i am after is a way to backfill the missing gaps with the figures from the higher level [Measure 1] so i would end up with this:

Project	DD	Value
AA	L	125
AA	M	125
BB	L	225
BB	M	100
CC	L	120
CC	M	124
DD	L	253
DD	M	253

Is this possible within PowerBI?

[DimaMD]

Hi @TimK Review the file and tell me if this is the desired result

[TimK]

DimaMD

Yes, but because of the complexity of my pbix model, i have to use the 2 x Measures [ARM_1] and [ARM_2] - it is not possible to use the original [Table 1] and [Table 2]

[TimK]

Can anyone help me with this at all - i am stuck!

[TimK]

Thanks DataInsights.........

Does not do the job - it does not fill out the blanks.... it returns my third table, but without the Pink text

Any other ideas?

[DimaMD]

@TimK this is what you want

[TimK]

yes, but as i say, please ignore the 2 tables............

i have to use the 2 x measures to create the answer......

If you can create a measure using the data in [Measure 1] and [Measure 2] that would be fantastic

[DimaMD]

@TimK there are no empty lines in the file you provided, Explain your problem in more detail

[TimK]

sorry,

here is the PBIX file again - FILE

So if you look at the tab [ARM_2] you will see that i have the tags [Project] and [DD]..... however, each [Project] tag does not have both [DD] tags - the L and the M (AA does; BB does not have the M etc)

What i am after is on the tab [Results] is to have for each [Project] the L and M tags

Where there is not a value from [Table 2], it takes the value from the corresponding [Project] in [Table 1]

So it looks like the results in the [Result] tab in Excel

Does that help??

[DimaMD]

@TimK Is this your desired outcome?

[DimaMD]

@TimK See the above post and file👆

[DataInsights]

Can you share a sample pbix via OneDrive, etc.?

[TimK]

DataInsights - thanks for your help.......

Sorry for the delay, i had to create an example (my actual PBIX file in on a network that i cannot export from - sorry)

So my example file is HERE

I have also attached the EXCEL here - and the desired output is on the tab [RESULTS]

I need to create the result using the 2 x Measures i have created [ARM_1] and [ARM_2] as these replicate what i have in the other model.

Thanks for your help

[DataInsights]

@TimK,

Try this measure. It returns the first non-blank of its arguments.

Merge Measure =
COALESCE ( [Measure 2], [Measure 1] )