Row based Column Flag

[shahzad667]

i want the desired column in dax calculated column or measure on my data i want to write dax to show my desire result as 

i have opened last topic that i got DAX 

=IF(CALCULATE(DISTINCTCOUNT(Table[Shift]),ALLEXCEPT(Table,Table[EMPLID]))>1,"Both","Single")

but in this dax i also want to exlude OnCall rows as welll so that my filter "Desired" column only show SINGle and Both but exclude on call as well 

Like if i select Single it should show 2 and if i select Both then it should show 2

in my dax it showing 4 when i Single

thanks in advance

[Anonymous · Accepted solution]

Hi @shahzad667 ,

I have created a simple sample, please refer to it to see if it helps you.

Create a column.

Column =
VAR _count =
    CALCULATE (
        COUNT ( 'Table'[shift] ),
        FILTER (
            'Table',
            'Table'[emplied] = EARLIER ( 'Table'[emplied] )
                && 'Table'[shift] <> "Oncall"
        )
    )
RETURN
    IF ( _count = 2, "Both", IF ( _count = 1, "Single", BLANK () ) )

Then create a measure.

Measure =
CALCULATE (
    DISTINCTCOUNT ( 'Table'[emplied] ),
    FILTER (
        ALL ( 'Table' ),
        'Table'[Column] <> BLANK ()
            && 'Table'[Column] = SELECTEDVALUE ( 'Table'[Column] )
    )
)

If I have misunderstood your meaning, please provide more details with your desired output and pbix file without privacy inforamtion.

Best Regards

Community Support Team _ Polly

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

[wdx223_Daniel]

calculated column code

=IF(Table[Shift]="OnCall","OnCall",IF(CALCULATE(DISTINCTCOUNT(Table[Shift]),ALLEXCEPT(Table,Table[EMPLID]),Table[Shift]<>"OnCall")>1,"Both","Single"))

[shahzad667]

Thats ok but in this senerio my filter will show 3 selection but in future if there will another shift will come like "halfday" then this will not work or i have to edit this dax , can it is possible that Dax will only entertain Single and Both Values only if there is Day and Night 

like 

Both will show if there is any day or night have more then one value , and single will show single value of day or night except all will not show or can be named "Others" 

hope you got my point