Forum Discussion

tristanweeks47's avatar
tristanweeks47
Frequent Visitor
2 years ago
Solved

IF and AND statements

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 

 

  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

     

2 Replies

  • Dangar332's avatar
    Dangar332
    Resident Rockstar
    2 years ago

    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

     

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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.