Forum Discussion

shahzad667's avatar
shahzad667
Frequent Visitor
3 years ago
Solved

Row based Column Flag

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's avatar
    Anonymous
    3 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    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's avatar
    wdx223_Daniel
    Community Champion
    3 years ago

    calculated column code

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

    • shahzad667's avatar
      shahzad667
      Frequent Visitor
      3 years ago

      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