how to calculate this column with at least condition

[Anonymous]

Hello i am trying to calculate the new red column based on two other columns. Can i achieve that

in Dax or power Query please ?

(it will then allow me to group by the first column with the third one)

Thanks

[speedramps · Accepted solution]

Try this ...

Answer =

Var HasNotWon =  

CALCULATE(

    ISEMPTY('Table'),

    ALLEXCEPT('Table','Table'[File]),

    'Table'[Status] = "Won"

)

RETURN

IF(HasNotWon , SELECTEDVALUE('Table'[Status]), "Won")

 How it works ..

VAR creates a temporary variable 

CALCULATE and  ALLEXCEPT('Table','Table'[File]) overides the default row filter context
then 'Table'[Status] = "Won" replies a different context

ISEMPTY then returns TRUE if the fileref has no Won records

The IF statement then RETURNs the current selected row status or Won.  

Thanks for reaching out for help.

I put in a lot of effort to help you, now please quickly help me by giving kudos.

Remember we are unpaid volunteers and here to coach you with Power BI and DAX skills and techniques, not do the users job for them. So please click the thumbs up and accept as solution button. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  I prefer to teach members on this forum techniques rather give full solutions and do their job. You can then adapt the technique for your solution, learn some DAX skills for next time and soon become a Power BI Super User like me. 

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

[Anonymous]

I am going to accept you as a solution because it works. But can you please explain to me something ? 

to understand your solution i tried this below. How does it detect that it should write 'False' at all F1 files? I mean isempty+"won' should put true at all "Lost". How does it work well :D. If you want me to ask this question in another post no problem

Answer =

Var HasNotWon =  

CALCULATE(

    ISEMPTY('Table'),

    'Table'[Status] = "Won"

)

return HasNotWon

[Anonymous]

I finally understood how it works after some researchs on the allexcept function : the "won" filters the table and the isempty gives us true if there is lost and false if it's true. Then the all except gives the results by keeping the file filter. As for F1 we had false for one file, all F1 rows are filtered as false.

[speedramps]

Ha Ha

I spent an hour writing and explanatioj before I saw your new message that you now understand it !

Here are my notes ... k 

Download this example which has a table with fileref, date and status
Click here to download 

Look at page1.

Note the Total1= default filter is fileref, date and status.

Note how REMOVEFILTERS and ALL alters the natural default row filtering so the total records for each fileref is counted. The natural status row filter has been overridden

Total1= COUNTROWS(yourtable)

Total2 =

CALCULATE(

    COUNTROWS(yourtable),

    REMOVEFILTERS(yourtable[Status])

)

Total3 =

CALCULATE(

    COUNTROWS(yourtable),

    ALL(yourtable[Status])

)

Now look at page2

Note how REMOVEFILTERS and ALL and yourtable[Status] = "Won"
alters the natural default row filtering
so all the fileref is counted but just for won

Won1 =

CALCULATE(

    COUNTROWS(yourtable),

        yourtable[Status] = "Won"

    )

Won2 =

CALCULATE(

    COUNTROWS(yourtable),

        REMOVEFILTERS(yourtable[Status]),

        yourtable[Status] = "Won"

    )

Won3 =

CALCULATE(

    COUNTROWS(yourtable),

        ALL(yourtable[Status]),

        yourtable[Status] = "Won"

    )

CALCULATE(

    COUNTROWS(yourtable),

        ALL(yourtable[Status]),

        yourtable[Status] = "Won"

    )

Now look at page3 which uses ISMPTY instead of countrows, because it is quicker.
But the CALCULATE logic is exactly the same as above.
Note how it overrides the natural filtering for the status but not the date

Answer1 =

CALCULATE(

    ISEMPTY(yourtable),

        All(yourtable[Status]),

        yourtable[Status] = "Won"

    )

This

Answer2 =

VAR HasNoWon =

CALCULATE(

    ISEMPTY(yourtable),

        All(yourtable[Status]),

        yourtable[Status] = "Won"

    )

RETURN

If(HasNoWon,

SELECTEDVALUE(yourtable[Status]),

"Won"

)

please click the thumbs up

if you need more help then raise a new ticket and quote @speedramps in the text.  I will then receive an automated notification.  I enjoy explaining DAX to people who are eager to learn.

[speedramps]

This is the second question  I have seen tonight about Win and Lose.

Are you both students preparing for an exam?  

If yes, then please do your home and dont cheat asking for the solution here, or you will easily be found out.

😀😀😀

Do this Power Bi free training 

[Anonymous]

No i am a working for a company as a data consultant and i use power bi for only 3 months. I did the most part of the job. So no i am not a student sorry 😄

Do you have any idea ? this is not really the data i have, so i replaced it with something else that you can help me easily.

Plus i can easily answer to this student question 😛 mine is more complex. I know how to used the conditions to say if Won then .. , but the "at least" part based on another column i couldn't find the solution