Forum Discussion
Merge Data created in Measures
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?
13 Replies
TimK,
Try this measure. It returns the first non-blank of its arguments.
Merge Measure = COALESCE ( [Measure 2], [Measure 1] )Thanks DataInsights.........
Does not do the job - it does not fill out the blanks.... it returns my third table, but without the Pink textAny other ideas?
Can you share a sample pbix via OneDrive, etc.?
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
sorry,
here is the PBIX file again - FILESo 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??
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
