Forum Discussion

RicardoSchmidt's avatar
RicardoSchmidt
Regular Visitor
4 years ago

Calculating a measure with a virtual table

Dear all,

 

I need to know the total produced for each "aplicação" filtered by a date (unique value) from another table using a virtual table.

 

I've made the two measures below. The firts one brings me the correct result for the total and can be filtered in the visual but it's not related to the "Aplicação". The second one have a relationship but it brings the total values without filters.

 

Novo Prog (pç) = CALCULATE(
SUM('fPosDiária_SRQ'[Prod.Peça]),FILTER(ALLSELECTED('fPosDiária_SRQ'),'fPosDiária_SRQ'[TURN_DATA_LOGICA]<SELECTEDVALUE('PD_Base PwBi'[dt_base])),VALUES(dAplicacao[Aplicação]))
 
Measure = SUMX(CALCULATETABLE(SUMMARIZE('fPosDiária_SRQ','fPosDiária_SRQ'[Aplicação],'fPosDiária_SRQ'[TURN_DATA_LOGICA] ,"Qtd", SUM('fPosDiária_SRQ'[Prod.Peça])),'Table 2'[TURN_DATA_LOGICA] < SELECTEDVALUE('PD_Base PwBi'[dt_base]) && 'Table 2'[TURN_DATA_LOGICA] > SELECTEDVALUE('PM Dia_Base PwBi'[dt_base]), TREATAS('Table 2',dAplicacao[Aplicação], 'fPosDiária_SRQ'[Prod.Peça] ,dCalendarioLiberacao[Data])),SUM('Table 2'[Qtd]))

 

 

Need help to understand why the measures above aren't working. I know the calculate/calculatetable "erases" the relationships. Can I make this calculation or do I need to use a physical table to find this value.

 

I don't know if I was clear, but appreciate some help.


Thanks,

Ricardo

 

Filter
Virtual Relationships
Virtual Table

3 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    4 years ago

    Please provide sanitized sample data that fully covers your issue. I can only help you with meaningful sample data.
    Please paste the data into a table in your post or use one of the file services like OneDrive or Google Drive. Screenshots of your source data are not useful.
    Please show the expected outcome based on the sample data you provided. Screenshots of the expected outcome are ok.

    https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
    https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523

    • RicardoSchmidt's avatar
      RicardoSchmidt
      Regular Visitor
      4 years ago

      Hi, sorry for that, I'll try to explain and put the samples better.

       

      I have a fact table with my daily production like below:

       

      TuboAplicaçãoProd. [ton]TDCA_CD_TP_MOVICADA_CD_TP_MOVIDESCR_TP_MOVICENT_CODTURN_DATA_LOGICAProd.Peça
      22 T 6380346001 33 0010,305101760SAÍDA FÁBRICA REPROCESSOCNT.0114/08/2022 00:001
      22 T 5961146020 22 0010,193101760SAÍDA FÁBRICA REPROCESSOCNT.0106/08/2022 00:001
      22 T 8490246030 45 0010,466101720SAÍDA BENEFICIAMENTOCNT.0215/08/2022 00:001
      22 T 8475949889 08 0010,133101720SAÍDA BENEFICIAMENTOCNT.0211/08/2022 00:001
      22 T 8474349889 07 0010,305101720SAÍDA BENEFICIAMENTOCNT.0108/08/2022 00:001
      22 T 8471949889 07 0010,122101720SAÍDA BENEFICIAMENTOCNT.0108/08/2022 00:001
      22 T 4773949912 03 0010,207101720SAÍDA BENEFICIAMENTOCNT.0205/08/2022 00:001

      Then I have another fact table with my planned production:

       

      centrodt_basediaProg. [pç]AplicaçãoOD x WT
      CNT.0215/08/202215/09/2022349888 0400136" x 1,5"
      CNT.0215/08/202215/09/2022249887 0900136" x 1,5"
      CNT.0215/08/202215/09/2022249887 0800136" x 1,5"
      CNT.0215/08/202215/09/20221149887 0600122" x 1,125"
      CNT.0215/08/202214/09/20222149887 0600122" x 1,125"
      CNT.0215/08/202213/09/20222249887 0600122" x 1,125"
      CNT.0215/08/202212/09/20222149887 0600122" x 1,125"
      CNT.0215/08/202211/09/2022549887 0600122" x 1,125"
      CNT.0215/08/202211/09/20221346001 3400136" x 1,5"
      CNT.0215/08/202210/09/2022246001 3400136" x 1,5"
      CNT.0215/08/202210/09/2022846001 3300136" x 1,5"
      CNT.0215/08/202210/09/2022146001 3300136" x 1,5"
      CNT.0215/08/202210/09/2022646001 3300136" x 1,5"
      CNT.0215/08/202209/09/20223049806 1200120" x 0,75"
      CNT.0215/08/202208/09/20223149806 1200120" x 0,75"
      CNT.0215/08/202207/09/20221949806 1200120" x 0,75"

      And I have the dimensions table for "Aplicação" and some others like Calendar and other keys.

       

       

      I need a measure that brings me the total produced until the fact table base date (column "dt_base") (<= than base date) (this date don't change until I upload a new program). This measure needs to show the values by "Aplicação" and needs to be sliced by other dimension table with keys.  Something like it:

       

       

       

       

       

       

      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        4 years ago

        sorry, I don't understand the ask.  Your measure uses a "Table 2"  that doesn't seem to be listed in the sample data.  Can you please check and explain your expected outcome again?