Forum Discussion

Mister_T's avatar
Mister_T
Advocate I
5 years ago
Solved

[DAX Measure] Correct values, but wrong total

Dear Community, again, I am lost in the depth of Power BI and DAX. Any help greatly appreciated! I have the following table 'Sales':   Deal ID Deal Closed Deal Won Reseller ...
Need Help
  • ERD's avatar
    ERD
    5 years ago

    Hi Mister_T ,

    Create a second measure:

     

    #Result = 
IF(HASONEVALUE('Sales'[Reseller]),
[COUNT_Reseller_Lost],
SUMX(VALUES('Sales'[Reseller]), [COUNT_Reseller_Lost])
)

     

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

amitchandak's avatar
amitchandak
Super User
5 years ago

Mister_T , Try

COUNT_Reseller_Lost =
CALCULATE (
DISTINCTCOUNT ( 'Sales'[Deal ID] ),
FILTER ('Sales' ,
coalesce(CALCULATE(Max('Sales'[Deal Closed]) , ALLEXCEPT ( 'Sales', 'Sales'[Deal ID] )), false()) =True(),
&& 'Sales'[Deal Won] = FALSE ()
)
)

  • Mister_T's avatar
    Mister_T
    Advocate I
    5 years ago

    Hi amitchandak,

     

    Thank you very much!

    Unfortunately, your DAX formula returns
    "The syntax for '&&' is incorrect. (DAX(CALCULATE (DISTINCTCOUNT ( 'Sales'[Deal ID] ),FILTER ('Sales' ,coalesce(CALCULATE(Max('Sales'[Deal Closed]) , ALLEXCEPT ( 'Sales', 'Sales'[Deal ID] )), false()) =True(),&& 'Sales'[Deal Won] = FALSE ()))))."

     

    Deleting the ',' after "TRUE ()",

     

    COUNT_Reseller_Lost_NEW = 
CALCULATE (
    DISTINCTCOUNT ( 'Sales'[Deal ID] ),
    FILTER ('Sales' ,
        COALESCE (
            CALCULATE(
                Max('Sales'[Deal Closed]) ,
                ALLEXCEPT ( 'Sales', 'Sales'[Deal ID] )
                ),
            FALSE ()
            )
        =TRUE ()
        && 'Sales'[Deal Won] = FALSE ()
        )
    )

     


    lets the formula pass as measure, but as soon as I want to use it in a vizual, I get the error message


    Any alternative to 'MAX' when it cannot work with BOOLEAN?

    Thanks a lot!

     

    • amitchandak's avatar
      amitchandak
      Super User
      5 years ago

      Mister_T , Try like

      COUNT_Reseller_Lost_NEW = CALCULATE ( DISTINCTCOUNT ( 'Sales'[Deal ID] ), FILTER ('Sales' , COALESCE ( CALCULATE( Count('Sales'[Deal Closed]), 'Sales'[Deal Closed] = TRue() , ALLEXCEPT ( 'Sales', 'Sales'[Deal ID] ) ), 0 ) <>0 && 'Sales'[Deal Won] = FALSE () ) )

      • Mister_T's avatar
        Mister_T
        Advocate I
        5 years ago

        Thank you, amitchandak! Your help is greatly appreciated.

        Unfortunatelly, here again, I get an error message, when using this measure as value for a table (or any other) visual:


        "The function COUNT cannot work with values of type Boolean."

         

        Best regards,

         

        Mister_T