topic Row based Column Flag in DAX Commands and Tips

Row based Column Flag

shahzad667 — Wed, 26 Oct 2022 06:35:53 GMT

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

Re: Row based Column Flag

wdx223_Daniel — Wed, 26 Oct 2022 06:49:23 GMT

calculated column code

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

Re: Row based Column Flag

shahzad667 — Wed, 26 Oct 2022 07:04:13 GMT

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

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

Re: Row based Column Flag

Anonymous — Thu, 27 Oct 2022 06:29:22 GMT

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.