Forum Discussion

felipevaz's avatar
felipevaz
Icon for Helper I rankHelper I
5 years ago
Solved

Using Two Excel Sheet Tables as filter in sum Dax

Hi,   I have three Excel tables: (start table, Codes and Subcodes)     Using Dax, I want to sum the field Value as a measure when I change the code and subcode (update all)...   All tabl...
  • amitchandak's avatar
    amitchandak
    5 years ago

    felipevaz , Try meausre like

     

    calculate(sum(table[value]),filter(table,table[codes] in values(codes[codes]) && table[subcode] in values(subcodes[subcodes])))

    calculate(sum(table[value]),filter(table,table[codes] in allselected(codes[codes]) && table[subcode] in allselected(subcodes[subcodes])))

amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
5 years ago

felipevaz , Ideally. You can join code and sub code with start table and they should filter automatically.

 

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

  • felipevaz's avatar
    felipevaz
    Icon for Helper I rankHelper I
    5 years ago

    https://unimedpoa-my.sharepoint.com/:x:/g/personal/felipe_vaz_unimedpoa_com_br/EV00g4eywn9Eq6J25nmH30gBFAecn6aE5HoOut5Z29QSaw?e=VytM1z 

    save to your computer the table was loaded into the model

    amitchandak I cant joining the tables cos that is costing a lot of time when refreshing....

     

    thats why I tried to load data into data model and use dax to filter the data

     

    I forgot to mention that I need to sum the total of value, sum the total of value of selected Code and subcode...

    • amitchandak's avatar
      amitchandak
      Icon for Super User rankSuper User
      5 years ago

      felipevaz , Try meausre like

       

      calculate(sum(table[value]),filter(table,table[codes] in values(codes[codes]) && table[subcode] in values(subcodes[subcodes])))

      calculate(sum(table[value]),filter(table,table[codes] in allselected(codes[codes]) && table[subcode] in allselected(subcodes[subcodes])))

      • felipevaz's avatar
        felipevaz
        Icon for Helper I rankHelper I
        5 years ago

        amitchandak worked like a charm! Really thank you for supporting me.