Alternative DAX Formula

[Anonymous]

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.

[v-jianboli-msft]

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.

[johnt75]

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]

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]

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

[Anonymous]

Sorry to say, It's highly confidential data

[johnt75]

what relationships do you have from your date table to the predictions table ? on which columns and are they one-to-many or many-to-many? in which directions do the filters flow ?

[Anonymous]

Its a single table, no other tables 

[Anonymous · Accepted solution]

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])))