Forum Discussion

AHS2019's avatar
AHS2019
Frequent Visitor
4 years ago
Solved

Create DAX formula for result

Hi, 

 

I am trying to create an measure to return a result of approved / not approved based on column K, C & L returning completed for each region for the specific id. 

 

My data set consists of multirows for each id in different regions and in if the columns for each I.D in column K, C & L is complete this will give a result of approved.

 

Could someone guide me on what Dax formula I should be using to achive this

 

TitleLocationKCLResult
100089654LondonCompletedCompletedCompletedApproved
100089654AngolaIncompleteIncompleteIncompleteNot Approved
100089654Côte d'IvoireIncompleteIncompleteIncompleteNot Approved
100089654DRCIncompleteIncompleteIncompleteNot Approved
100089654GhanaIncompleteIncompleteIncompleteNot Approved
100089654KenyaIncompleteIncompleteIncompleteNot Approved
100089654MauritiusIncompleteIncompleteIncompleteNot Approved
100089654MozambiqueIncompleteIncompleteIncompleteNot Approved
100089654NamibiaIncompleteIncompleteIncompleteNot Approved
100089654NigeriaIncompleteIncompleteIncompleteNot Approved
100089654South AfricaIncompleteIncompleteIncompleteNot Approved
100089654TanzaniaIncompleteIncompleteIncompleteNot Approved
100089654UgandaIncompleteIncompleteIncompleteNot Approved
100089654ZambiaIncompleteIncompleteIncompleteNot Approved
531766404LondonCompletedCompletedCompletedApproved
531766404DubaiCompletedCompletedCompletedApproved
531766404Côte d'IvoireCompletedCompletedCompletedApproved
531766404DRCCompletedCompletedCompletedApproved
531766404GhanaCompletedCompletedCompletedApproved
531766404KenyaCompletedCompletedCompletedApproved

 

Need Help
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Result CC =
VAR _condition = Data[K] = "Completed"
    && Data[C] = "Completed"
    && Data[L] = "Completed"
RETURN
    IF ( _condition, "Approved", "Not approved" )
  • Jihwan_Kim's avatar
    Jihwan_Kim
    4 years ago

    Hi,

    Thank you for your message, and could you please check the below?

    Or, please show how your desired outcome of the visualization looks like.

     

    Fix Result measure: =
VAR _condition =
    "Completed"
        IN VALUES ( Data[K] )
            && NOT ( "Incomplete" IN VALUES ( Data[K] ) )
                && "Completed"
                    IN VALUES ( Data[C] )
                        && NOT ( "Incomplete" IN VALUES ( Data[C] ) )
                            && "Completed"
                                IN VALUES ( Data[L] )
                                    && NOT ( "Incomplete" IN VALUES ( Data[L] ) )
RETURN
    IF (
        HASONEVALUE ( Data[Title] ),
        IF ( _condition, "Approved", "Not approved" )
    )

4 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Icon for Super User rankSuper User
    4 years ago

    Hi,

    I am not sure if I understood your question correctly, but please check the below picture and the attached pbix file.

    It is for creating a new column.

     

     

    Result CC =
VAR _condition = Data[K] = "Completed"
    && Data[C] = "Completed"
    && Data[L] = "Completed"
RETURN
    IF ( _condition, "Approved", "Not approved" )
  • AHS2019's avatar
    AHS2019
    Frequent Visitor
    4 years ago

    Thank you for your help however I am trying to use the formala to filter on the title and provide a overall result to show approved if column k, c,l is complete for all rows which have the same title

    • Jihwan_Kim's avatar
      Jihwan_Kim
      Icon for Super User rankSuper User
      4 years ago

      Hi,

      Thank you for your message, and could you please check the below?

      Or, please show how your desired outcome of the visualization looks like.

       

      Fix Result measure: =
VAR _condition =
    "Completed"
        IN VALUES ( Data[K] )
            && NOT ( "Incomplete" IN VALUES ( Data[K] ) )
                && "Completed"
                    IN VALUES ( Data[C] )
                        && NOT ( "Incomplete" IN VALUES ( Data[C] ) )
                            && "Completed"
                                IN VALUES ( Data[L] )
                                    && NOT ( "Incomplete" IN VALUES ( Data[L] ) )
RETURN
    IF (
        HASONEVALUE ( Data[Title] ),
        IF ( _condition, "Approved", "Not approved" )
    )
      • AHS2019's avatar
        AHS2019
        Frequent Visitor
        4 years ago

        Thank you that works, on the filter if there is no selection the card displays blank, how can I update the  to display measure to read "client not selected" if no selection has been made