Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

Alternative DAX Formula

Hi Everyone,

Currently I am using one formula to find the closed deals based on date.

I have created a stacked bar chart which can give the closed deals count based on date.

I have used this formula " 

CALCULATE(DISTINCTCOUNT(pred[deal]) ,pred[ACTUAL_STATUS(deal_status)] <> "Open", FILTER(pred,pred[PREDICTION_DATE] = CALCULATE(max(pred[PREDICTION_DATE]),ALLEXCEPT(pred,pred[deal])))) " 
I am gettng the accurate data, but this caluclation is taking more time to load the visual.
 
Can anyone suggest alternative formula for the above mentioned?
 
Thanks in advance.
Need Help
Show and Tell
Tips and Tricks
  • Anonymous's avatar
    Anonymous
    4 years ago

    Thanks for your hep and it resolved now. I used different aproach to solve it

    CALCULATE (
    DISTINCTCOUNT (pred[deal] ),
    pred[ACTUAL_STATUS] <> "Open",keepfilters(filter(pred, pred[MAX_CLOSED_DATE]=pred[PREDICTION_DATE])))

8 Replies

  • johnt75's avatar
    johnt75
    Super User
    4 years ago

    You could try

    Closed deals =
VAR DealAndDate =
    ADDCOLUMNS (
        VALUES ( pred[deal] ),
        "@date", CALCULATE ( MAX ( pred[PREDICTION_DATE] ) )
    )
VAR Result =
    CALCULATE (
        DISTINCTCOUNT ( pred[deal] ),
        pred[ACTUAL_STATUS(deal_status)] <> "Open",
        DealAndDate
    )
RETURN
    Result
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Thanks for your response, it is giving the total count correctly. But when you plot the visual ( stacked bar: date: X-axis, Cout- Y-axis)it is not giving the exact count of deals which got closed on perticular date.
      It is showing as summation for previous days count

      • johnt75's avatar
        johnt75
        Super User
        4 years ago

        is it possible to share a PBIX with any confidential information removed ?

  • v-jianboli-msft's avatar
    v-jianboli-msft
    Community Support
    4 years ago

    Hi Anonymous 

     

    If your problem has been solved, kindly Accept it as the solution. More people will benefit from it. Or if you are still confused about it, please provide me with more details about your table and your problem or share me with your pbix file after removing sensitive data.

     

    Refer to:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

     

    Best Regards,

    Jianbo Li

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