Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
 View Only
  • 1.  Error in DAx

    Posted Apr 08, 2022 12:07 PM
    This is my CALCULATED COLUMN:

    IF('Family'[Product Family]="XXX" || 'Prod'[Team-Prod]="YYY"
    AVERAGEX(
    FILTER(
    Tablename,
    AND(Tablename [assignment id],
    AND(Tablename [Status ]=CLOSED,
    AND(Tablename [Type ]="A",
    AND(Tablename [Effort time]<>0,
    Tablename [ Time]<>BLANK()
    ))))),
    Tablename [Effort Time]
    ),
    AVERAGEX(
    FILTER(
    Tablename,
    AND(Tablename [Primary Team id],
    AND(Tablename [Status]="Closed",
    AND(Tablename [Product Family]<>"XXX",
    AND(Tablename [Product Family ]<>"YYY",
    AND(Tablename [effort time]<>0,
    Tablename [Effort time]<>BLANK()
    )
    )
    )
    ) ) ),
    Tablename [ Effort time] ))



    I am trying to do in measure
    IF(MAX('Family'[Product Family]="XXX" || MIN('Prod'[Team-Prod]="YYY"
    AVERAGEX(
    FILTER(
    Tablename,
    AND(Tablename [assignment id],
    AND(Tablename [Status ]=CLOSED,
    AND(Tablename [Type ]="A",
    AND(Tablename [Effort time]<>0,
    Tablename [ Time]<>BLANK()
    )
    )
    )
    )
    ),
    Tablename [Effort Time]
    ),
    AVERAGEX(
    FILTER(
    Tablename,
    AND(Tablename [Primary Team id],
    AND(Tablename [Status]="Closed",
    AND(Tablename [Product Family]<>"XXX",
    AND(Tablename [Product Family ]<>"YYY",
    AND(Tablename [effort time]<>0,
    Tablename [Effort time]<>BLANK()
    )
    )
    )
    ) ) ),
    Tablename [ Effort time] ))

    But I got error as  Too few arguments were passed to IF condition.The minimum argument for function is 2.

    Can you please help to sort this error. and let me know any changes.?

    ------------------------------
    ritika george
    powerbi
    ------------------------------


  • 2.  RE: Error in DAx

    Top Contributor
    Posted Apr 11, 2022 07:03 AM
    Edited by Mehdi HAMMADI Apr 11, 2022 08:41 AM
    Hi @ritika george,

    Generally these kind of errors comes from typo misspelling and from some commas or parentheses that are missed.

    My advise to you is to take your measure and first try to format it using DAX Formatter because there is a syntax error DAX Formatter will not be able to format it but by checking the measure peace by peace you will find where is the error and this is how I proceeded.

    I started with the FILTER part, then I switched to the AVERAGEX and I finished with the whole measure.

    Try this and let me know if it solve your problem and note that I do not understand what you are trying with these two condition 'Tablename'[assignment id] and 'Tablename'[Primary Team id]. for me some thing is missing like 'Tablename'[assignment id] = "something"

    Your Column =
    IF (
        'Family'[Product Family] = "XXX"
            || 'Prod'[Team-Prod] = "YYY", // the comma was missed
        AVERAGEX (
            FILTER (
                'Tablename',
                AND (
                    'Tablename'[assignment id],
                    AND (
                        'Tablename'[Status] = "Closed",
                        AND (
                            'Tablename'[Type] = "A",
                            AND (
                                'Tablename'[Effort time] <> 0,
                                'Tablename'[Time]
                                    <> BLANK ()
                            )
                        )
                    )
                )
            ),
            'Tablename'[Effort Time]
        ),
        AVERAGEX (
            FILTER (
                'Tablename',
                AND (
                    'Tablename'[Primary Team id],
                    AND (
                        'Tablename'[Status] = "Closed",
                        AND (
                            'Tablename'[Product Family] <> "XXX",
                            AND (
                                'Tablename'[Product Family] <> "YYY",
                                AND (
                                    'Tablename'[Effort time] <> 0,
                                    'Tablename'[Time]
                                        <> BLANK ()
                                )
                            )
                        )
                    )
                )
            ),
            'Tablename'[Effort Time]
        )
    )



    ------------------------------
    If any discussion or answer help, please mark it as "Recommend". If it solve the problem, mark it as "Best answer"
    ------------------------------

    Best regards
    Mehdi HAMMADI

    MCT | MCSA BI Reporting | MCSE Data Management and Analytics | Data Analyst Associate
    2019 Ruby Award Dynamic Communities
    ------------------------------



  • 3.  RE: Error in DAx

    Silver Contributor
    Posted Apr 11, 2022 01:14 PM
    It appears you're missing something in the first argument of this filter.

    AVERAGEX(
    FILTER(
    Tablename,
    AND(Tablename [Primary Team id],
    AND(Tablename [Status]="Closed",
    AND(Tablename [Product Family]<>"XXX",
    AND(Tablename [Product Family ]<>"YYY",
    AND(Tablename [effort time]<>0,
    Tablename [Effort time]<>BLANK()

    What criteria do you need to pull from the [Primary Team id]? Currently you list it, but you're not making it equal to, not equal to, etc.

    ------------------------------
    Nancy Peterson
    Senior Business Analyst
    Pitsco Education
    Pittsburg KS
    ------------------------------



  • 4.  RE: Error in DAx

    Bronze Contributor
    Posted Apr 12, 2022 03:20 AM

    Looks like you are missing commas and closing brackets on the MIN/MAX functions, if what you are trying to do is

    IF(MAX('Family'[Product Family])="XXX" || MIN('Prod'[Team-Prod])="YYY",
        AVERAGE(....),
        AVERAGE(...)
    )



    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 5.  RE: Error in DAx

    Posted Apr 12, 2022 12:27 PM

    MIN and/or MAX can be used on numbers/dates.

    I am not sure if [Product Family] and [Team-Prod] are numbers or text. Names suggest they are text.
    If they are text there is no point to use MAX/MIN function.

    Unless I dont know how to (why) use it on text fields.
    Any thoughts?

    Regards.



    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 6.  RE: Error in DAx

    Bronze Contributor
    Posted Apr 13, 2022 05:02 AM
    Yes, Max & Min can be used on text, because they are sortable data.

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------



  • 7.  RE: Error in DAx

    Posted Apr 14, 2022 01:13 PM
    Hi James,

    Thx for answer.
    So it's works like 'First' and 'Last'. But I still can't see logic with see A or Z. What a point is see all time Adam  or Zachary with no chance to see James :)
    Any example where it can be useful?

    Regards

    ------------------------------
    Adam Artur Boltryk
    Business Analyst
    ------------------------------



  • 8.  RE: Error in DAx

    Bronze Contributor
    Posted Apr 18, 2022 04:24 AM
    This is usually done to return a single value from a column (ie from multiple rows) where it has a single value, for calculations that require a value but not a list.

    ------------------------------
    James Watts
    Business Intelligence Analyst
    ------------------------------