Forum Discussion

schoden's avatar
schoden
Icon for Post Partisan rankPost Partisan
6 years ago
Solved

IF with conditions

Hi Experts, Help me out with this formula . I have two tables;
table[company],table[Sales],         table2[branch]…..
If company selected is A , return sales amount otherwise return sales amount for all other company BUT apply filter branch<> z
 
new column= IF(selectedvalue[company])=''A', SUM(table[Sale]) , CALCUALTE(SUM(table[Sale], NOT(CONTAINSTRINGS(table2[branch]),"z" )))
Need Help
  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    6 years ago

    Hi schoden ,

     

    We can create a new slicer based on a new distinct company table to meet your requirement.

     

    1. Create a new table based on company.

     

    distinct company = DISTINCT('Table'[company])

     

     

    2. Then we can create a slicer based on the company.

     

     

    3. At last we create a measure, and the result like this,

     

    Measure = IF(SELECTEDVALUE('distinct company'[company])="A", SUM('Table'[Sales]) , CALCULATE(SUM('Table'[Sales]), NOT(CONTAINSSTRING('Table (2)'[branch],"z"))))

     

     

     

    There is no relationship between Table and distinct table.

     

    If it doesn’t meet you requirement, could you please show the exact expected result based on the table that we have shared?

    BTW, pbix as attached.       

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

4 Replies

    • schoden's avatar
      schoden
      Icon for Post Partisan rankPost Partisan
      6 years ago

      amitchandak  Hi thanks for the reply.

       

      Yes it is measure I applied,  sorry I wrote as column.  

      It is not filtering ...do you have better DAX for it.   ? 

  • v-zhenbw-msft's avatar
    v-zhenbw-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    Hi schoden ,

     

    We can create a new slicer based on a new distinct company table to meet your requirement.

     

    1. Create a new table based on company.

     

    distinct company = DISTINCT('Table'[company])

     

     

    2. Then we can create a slicer based on the company.

     

     

    3. At last we create a measure, and the result like this,

     

    Measure = IF(SELECTEDVALUE('distinct company'[company])="A", SUM('Table'[Sales]) , CALCULATE(SUM('Table'[Sales]), NOT(CONTAINSSTRING('Table (2)'[branch],"z"))))

     

     

     

    There is no relationship between Table and distinct table.

     

    If it doesn’t meet you requirement, could you please show the exact expected result based on the table that we have shared?

    BTW, pbix as attached.       

     

    Best regards,

     

    Community Support Team _ zhenbw

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.