Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

DAX function to check if data is consistent

Hi there!

 

I have a huge table with employee data from two data sources (SharePoint, SAP).

My data looks like this:

IDNameCCEntry dateLeaving dateDivBUQuitting reasonQuitting typeWork hoursFTELocationPositionDataSource
563475Paul Smith7310212019. 03. 04.2021. 04. 18.FinanceMCCKresignationVoluntary401TorontoApplication AdministratorSharePoint
563475Paul Smith7310212019. 03. 04.2021. 04. 18.FinanceMCCKresignationVoluntary401TorontoApplication AdministratorSAP

I need to compare the data from the two sources. I need a function that compares the two rows for each employee and checks if there's difference in any column apart from the DataSource. 

The output should be in a new column that indicates if there's an inconsistency in the data. It would be nice if there's a possibility to color the cells with the deviations or something like this.

Any idea how to achive this with DAX?

Thanks a lot.

 

  • tamerj1's avatar
    tamerj1
    4 years ago

    Anonymous 
    This is the code for a calculated column

     

    Any Differences? = 
VAR DistinctRows =
    COUNTROWS (
        DISTINCT (
            CALCULATETABLE (
                SELECTCOLUMNS (
                    'Employee Data',
                    "@Name", [Name], "@CC", [CC], "@EntryDate", [Entry date], "@LeavingDate", [Leaving date],
                    "@@Div", [Div], "@BU", [BU], "@QuittingReason", [Quitting reason], "@QuittingType", [Quitting type],
                    "@WorkingHours", [Work hours], "@FTE", [FTE], "@Location", [Location], "@position", [Position]
                ),
                ALLEXCEPT ( 'Employee Data', 'Employee Data'[Name] )
            )
        )
    )
RETURN
    IF ( DistinctRows = 2, "Yes", "" )

     

6 Replies

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Hi Anonymous 

    How does the report look like? or just the same as the data sorce table?

  • tamerj1's avatar
    tamerj1
    Community Champion
    4 years ago

    Anonymous 
    Not sure what exactly you're looking for but following is one way of doing that.

    Differences? = 
VAR DistinctRows =
    COUNTROWS (
        DISTINCT (
            FILTER (
                ALLEXCEPT ( 'Employee Data', 'Employee Data'[DataSource] ),
                'Employee Data'[ID] = SELECTEDVALUE ( 'Employee Data'[ID] )
            )
        )
    )
RETURN
    IF ( DistinctRows = 2, "Yes", "" )
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      I get the following error trying your solution: 

      A circular dependency was detected: Employee_Data[Differences?].

      The output should be a new column in the data source table as you did it. 

      It would be useful to have an additional column, which is empty by default, but in case of difference it would show the name of the column that differs.

      • tamerj1's avatar
        tamerj1
        Community Champion
        4 years ago

        Anonymous 
        This is the code for a calculated column

         

        Any Differences? = 
VAR DistinctRows =
    COUNTROWS (
        DISTINCT (
            CALCULATETABLE (
                SELECTCOLUMNS (
                    'Employee Data',
                    "@Name", [Name], "@CC", [CC], "@EntryDate", [Entry date], "@LeavingDate", [Leaving date],
                    "@@Div", [Div], "@BU", [BU], "@QuittingReason", [Quitting reason], "@QuittingType", [Quitting type],
                    "@WorkingHours", [Work hours], "@FTE", [FTE], "@Location", [Location], "@position", [Position]
                ),
                ALLEXCEPT ( 'Employee Data', 'Employee Data'[Name] )
            )
        )
    )
RETURN
    IF ( DistinctRows = 2, "Yes", "" )

         

  • CNENFRNL's avatar
    CNENFRNL
    Community Champion
    4 years ago

    DAX is for calculation whereas PQ is for such data cleansing/shaping tasks.