Forum Discussion

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
7 years ago
Solved

URGENT! How to filter data in different table?

Hi all,   I have three tables: rawdata, date and type. In the rawdata, I added a new column whose name is "New Type". Then I linked the "New Type" column to the "type" column in type table.   I w...
Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    7 years ago

    hi, ryan_mayu

    Please try this formula,

    test = 
VAR maxdate=MAX('Rawdata'[date])
VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01)
VAR totalamount=sum(Rawdata[amount])
VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type]),'Rawdata'[date]=PM1))
RETURN
PM1TOTAL

    In your conditional, ALLEXCEPT(Rawdata,Rawdata[New Type]) , so only Rawdata[New Type] can filter the data.

    you just add a conditional for 'Type table'[type] in ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type])

     

    Result:

     

    Best Regards,

    Lin

     

ryan_mayu's avatar
ryan_mayu
Icon for Super User rankSuper User
7 years ago

Can anyone help me on this? It's really urgent. Thanks in advance

v-lili6-msft's avatar
v-lili6-msft
Icon for Community Support rankCommunity Support
7 years ago

hi, ryan_mayu

Please try this formula,

test = 
VAR maxdate=MAX('Rawdata'[date])
VAR PM1 = DATE(YEAR(maxdate),MONTH(maxdate)-1,01)
VAR totalamount=sum(Rawdata[amount])
VAR PM1TOTAL=CALCULATE([totalamount],FILTER(ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type]),'Rawdata'[date]=PM1))
RETURN
PM1TOTAL

In your conditional, ALLEXCEPT(Rawdata,Rawdata[New Type]) , so only Rawdata[New Type] can filter the data.

you just add a conditional for 'Type table'[type] in ALLEXCEPT(Rawdata,Rawdata[New Type],'Type table'[type])

 

Result:

 

Best Regards,

Lin

 

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    7 years ago

    Hi Lin,

     

    It works. Many thanks for your help. Really apprecated that.