IF with conditions

[schoden]

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" )))

[v-zhenbw-msft · Accepted solution]

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.

[schoden]

@v-zhenbw-msft 

Thank you.

This is exactly my requirement.  

[amitchandak]

@schoden , You have to create a new measure

new Measure= IF(selectedvalue[company]="A", SUM(table[Sale]) , CALCUALTE(SUM(table[Sale]), NOT(CONTAINSTRINGS(table2[branch],"z" ))))

Similar example

https://community.powerbi.com/t5/Desktop/Slicer-MTD-QTD-YTD-to-filter-dates-using-the-slicer/td-p/50...

[schoden]

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