Tricky: Filter measure from different datasets

[Quentin]

Very tricky and quite challenging...
Fist of all, fan of the composite data model! Let's see how far we can take it
I have several datasets published that countains differents measures (main KPIs per department- more or less one dataset per department)

How can i do to create a report that gather the actual value of these measures from the different datasets, list the measure and have a filter experience...
I am able to import the diferent dataset and create visual per visual each measure, but how could i list them in a table for example and allow to filter that table...
Very tricky indeed if anyone has a clue, very welcome!

[v-cazheng-msft]

Hi @Quentin 

You can create a Calculated table to put these Measures into one table.

Table =

var t1=ROW("source dataset","item sales","item total",[item total])

var t2=ROW("source dataset","store sale amount","amount total",[amount total])

return UNION(t1,t2)

The result looks like this:

Here is the pbix.

Best Regards,

CaiyunnZhengg

Is that the answer you're looking for? If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Quentin]

Hello @v-cazheng-msft,
Coming back on this subject, the problem is that calculated tables are not supported on composite model for the shedule refresh in the service...
Therefore i had to contruct visual by visual selecting the correct measure and playing with bookmark to allow filtering experience...
Not perfect for the moment...

[Quentin]

Hello CaiyunnZhengg and thanks for your support

This is brillant and we are getting closer! 
However it doesn't fully answer my need as the problem i always have with calculated tables is that they do not respond to filters as they are calculated before being used as input in visuals

Therefore with this technique the calculated table takes the measure [item total] without it to be filtered 

If trying to use the Calculate function to filter the result of the measure before getting it into the calculated table:

Test =

var t1=ROW("source dataset","% Signature Objective GESPRA","Value",CALCULATE([% Signature Objective GESPRA],CONTRACT_EXTENSION[Analysis Date.Year]=YEAR(TODAY())))

var t2=ROW("source dataset","% of Total Volume Signed on Consortia","Value",CALCULATE([% of Total Volume Signed on Consortia],CONTRACT_EXTENSION[Analysis Date.Year]=YEAR(TODAY())))

return UNION(t1,t2)

I recieve the following error i don't understand...
La columna "CONTRACT_EXTENSION"[Analysis Date.Year] no se puede insertar en el origen de datos remoto y no se puede usar en este escenario.

Which can be translated as the column cannot be inserted in the remote origin of data and cannot be used in this context...

Looking forward if you have any other tips this first one was brillant

Cheers,