I want to create a custom dimension as follows.
So I write
WHEN COUNTRY = 'UK' THEN 'UK'
WHEN COUNTRY = 'DE' THEN 'DE'
WHEN COUNTRY = 'FR' THEN 'FR'
WHEN COUNTRY IN ('DE','FR') THEN 'DE&FR'
As expected this custom dimension does not help to sum the measures for DE & FR.
How can I achieve this at universe level?
Peter Huyghe replied
I don't think it will work that way. If I understood well, Steve wants to display fact data for DE and FR twice.
One time by individual country, a second time by country group.
I would solve this in the report, by making a subtotal when the country is DE or FR.
This could also be solved in the universe, but I don't consider the solution very good practice. One could for instance make a derived table with something like :
select country, sum(measure1), sum(measure2) from fact
select 'DE+FR', sum(measure1), sum(measure2) from fact
where country in ('DE','FR')
This way fact data is actually twice in the result set. There is of course the danger that fact data is accidently aggregated when the country dimension is not taken into the result set.