locked
Error - Trying to filter multiple Columns - No, No, No RRS feed

  • Question

  • Hi,

    I think I understand what I am doing wrong, but not sure how to fix. 

    My model has two tables. Trying to constrain my distinct count to the below parameters. 
    I'm using a column from both tables, but it doesn't like that. I am guessing I need to use FILTER(), but not sure where. 

    =
    CALCULATE (
        DISTINCTCOUNT ( Trip_Airway_Qualifiers[Unique ID] ),
        Trip_Airway_Qualifiers[value_text] = "Yes" &&
        Trip_Interventions[intervention_code] = 389 ||
        Trip_Interventions[intervention_code] = 474 ||
        Trip_Interventions[intervention_code] = 386
    )
    

    Error Message:

    Calculation error in measure 'Trip_Airway_Qualifiers'[Yes]: The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

    Thank you for any help. 


    Brent

    Wednesday, February 17, 2016 2:42 AM

Answers

  • Any filter argument to CALCULATE() must reference only one column. You have used logical operators to combine multiple logical expressions (touching multiple columns) into argument 2 of your CALCULATE().

    Luckily, there's a trivial solution for you. CALCULATE() takes 1-N arguments. Arguments 2-N must be filter expressions that are all evaluated in a logical and. You can literally replace your && with a comma.

    =
    CALCULATE (
        DISTINCTCOUNT ( Trip_Airway_Qualifiers[Unique ID] ),
        Trip_Airway_Qualifiers[value_text] = "Yes",
        Trip_Interventions[intervention_code] = 389 ||
        Trip_Interventions[intervention_code] = 474 ||
        Trip_Interventions[intervention_code] = 386
    )


    Check out the Power BI User Group of Philadelphia.
    Our next meeting is April 7 in Malvern, PA.

    • Marked as answer by bvanscoy678 Wednesday, February 17, 2016 9:59 PM
    Wednesday, February 17, 2016 7:00 PM

All replies

  • Any filter argument to CALCULATE() must reference only one column. You have used logical operators to combine multiple logical expressions (touching multiple columns) into argument 2 of your CALCULATE().

    Luckily, there's a trivial solution for you. CALCULATE() takes 1-N arguments. Arguments 2-N must be filter expressions that are all evaluated in a logical and. You can literally replace your && with a comma.

    =
    CALCULATE (
        DISTINCTCOUNT ( Trip_Airway_Qualifiers[Unique ID] ),
        Trip_Airway_Qualifiers[value_text] = "Yes",
        Trip_Interventions[intervention_code] = 389 ||
        Trip_Interventions[intervention_code] = 474 ||
        Trip_Interventions[intervention_code] = 386
    )


    Check out the Power BI User Group of Philadelphia.
    Our next meeting is April 7 in Malvern, PA.

    • Marked as answer by bvanscoy678 Wednesday, February 17, 2016 9:59 PM
    Wednesday, February 17, 2016 7:00 PM
  • Hi,

    That seemed simple enough. I am still not getting the results I would expect, but it must be something else I am doing incorrectly. 

    Thank you,

    Brent



    Brent



    • Edited by bvanscoy678 Wednesday, February 17, 2016 9:59 PM
    Wednesday, February 17, 2016 9:46 PM
  • If you'd like to start up a new thread with a description of your model and some sample output of current state and expectation, we'd be able to help define the appropriate measure logic.

    Check out the Power BI User Group of Philadelphia.
    Our next meeting is April 7 in Malvern, PA.

    Thursday, February 18, 2016 4:18 PM
  • @greggyb is half correct.  If you NEED the OR  and/or AND operators, you will need to create separate calculations for filters that do not come from the same column.

    Take the following as an example I just corrected:

    Code with error- 

    CALCULATE([Finance Value],
    
    'GL Account Hierarchy'[L3]="aaaa" ||
    
    'GL Account Hierarchy'[L3]="bbbb"||
    
    'GL Account Hierarchy'[L5]="cccc" ||
    
    'GL Account Hierarchy'[L5]="dddd",
    
    ALL('*GL Account Hierarchy Alt'),
    
    ALL('GL Account Hierarchy')
    
    )

    Code with out error-

    CALCULATE([Finance Value],
    
    'GL Account Hierarchy'[L3]="aaaa" ||
    
    'GL Account Hierarchy'[L3]="bbbb",
    
    ALL('*GL Account Hierarchy Alt'),
    
    ALL('GL Account Hierarchy')
    
    )
    
    
    +
    
    
    
    CALCULATE([Finance Value],
    
    
    'GL Account Hierarchy'[L5]="cccc" ||
    
    
    'GL Account Hierarchy'[L5]="dddd",
    
    
    ALL('*GL Account Hierarchy Alt'),
    
    
    ALL('GL Account Hierarchy')
    
    
    )

    Notice how I cant' make an OR statement for  L3 and L5.  These two are separate columns and therefor cannot contain an OR and AND operator within the same calculation.  You must instead create two separate calculations.

    Hope this helps,

    Eric




    Thursday, May 23, 2019 3:59 PM
  • I know this post is very old, but I'm running into a similar issue an am unable to find a way to work around it! I have a measure which uses OR statements, but one of those OR statements has two conditions. I have no idea how to address this. I've placed the code below:

    ASMs Flown:=  CALCULATE( 
    	SUMX( DimFlightLeg, DimFlightLeg[Capacity] * DimFlightLeg[StageLength])
        , DimFlightLeg[OperatorCode] = "SY"	
        , DimFlightLeg[Status] = 1 || DimFlightLeg[Status] = 5 || DimFlightLeg[Status] = 0 && DimFlightLeg[DepartureDate] < "06/18/2019"
    	, DimFlightLeg[Flight as a Number] < 8000 &&
        NOT (DimFlightLeg[Flight as a Number] >= 1000 &&
            DimFlightLeg[Flight as a Number] <= 1099
            )
    	, USERELATIONSHIP( DimDateDeparture[Departure_Date], DimFlightLeg[DepartureDate])
    	, USERELATIONSHIP( DimFlightLeg[FlightLegID], FactPassengerLeg[FlightLegID] )
    )

    Not sure how to address this. This measure generates the following error:

    Severity Code Description Project File Line Suppression State
    Warning Measure 'Flown'[ASMs Flown] : The expression contains multiple columns, but only a single column can be used in a True/False expression that is used as a table filter expression.

    Thanks for any help!!


    A. M. Robinson

    Friday, August 21, 2020 12:51 PM