Forum Discussion

Quentin's avatar
Quentin
Helper IV
5 years ago

Tricky: Filter measure from different datasets

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 p...
Need Help
v-cazheng-msft's avatar
v-cazheng-msft
Community Support
5 years ago

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's avatar
    Quentin
    Helper IV
    5 years ago

    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,

     

  • Quentin's avatar
    Quentin
    Helper IV
    5 years ago

    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...