IF and AND statements

[tristanweeks47]

Hello, 
I am trying to show UPRNS that have account type Closed AND Connection. 
Please see screenshot below...
The UPRNS is a list of duplicates. I want to show only the UPRNS of the account that have closed and then there is a connection after. How do i go about this? Thank you 

[v-rzhou-msft · Accepted solution]

Hi @tristanweeks47 ,

Please try code as below to create a measure to filter your visual.

Measure = 
VAR _TYPE_LIST = CALCULATETABLE(VALUES('Table'[Account Type]),ALLEXCEPT('Table','Table'[UPRN]))
RETURN
IF("Closed" in _TYPE_LIST && "Connection" in _TYPE_LIST ,1,0)

Sample:

Add this measure into visual level filter and set it to show items when value = 1.

Best Regards,
Rico Zhou

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

[Dangar332]

HI, @tristanweeks47 

try below code 

adjust column name and table name
logic work below

Measure = 
var a1 = CALCULATE(max('Table'[Column1]),FILTER('Table', 'Table'[Column2]=2))
var b1 = CALCULATE(max('Table'[Column1]),FILTER('Table', 'Table'[Column2]=1))
return 
CALCULATE(MAX('Table'[Column1]),
 'Table'[Column1]=a1 && 'Table'[Column1]=b1,ALLEXCEPT('Table','Table'[Column2]))

sample data i have taken

result of measure