Forum Discussion

sarogersz's avatar
sarogersz
Regular Visitor
9 years ago
Solved

(DAX) Problem - Data Profiling Measures

I have a single table, key column is Lot Number.  Each row has a Status column, of which there are 7 Distinct values.  Each Lot is supposed to have a single row with Start of Run status AND a single row with End of Run status.

 

I have measures that give me numeric counts that aid inspection for data quality issues, but I want to create some measures like the following:

  1. Count of Lots that have Start of Run entry without an End of Run entry
  2. Count of Employees that have entered End of Run row without entering a Start of Run row
  3. etc.

The basic problem is how to evaluate conditions that involve a set of rows in a table based on logical tests across those rows.

 

Where do I start in doing these kinds of measures?

 

Thanks for any help on this.

 

Stephen

 

Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    9 years ago

    Hi sarogersz,

     

    Maybe your data looks like this:

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Then you could try these three formulas:

    OnlyHaveStart =
SUMX (
    SUMMARIZE (
        'Table1',
        'Table1'[Lot Number],
        "IfHit", IF (
            "Start of Run" IN VALUES ( 'Table1'[Status] )
                && NOT "End of Run" IN VALUES ( 'Table1'[Status] ),
            1,
            0
        )
    ),
    [IfHit]
)
    OnlyHaveEnd =
SUMX (
    SUMMARIZE (
        'Table1',
        'Table1'[Lot Number],
        "IfHit", IF (
            NOT "Start of Run" IN VALUES ( 'Table1'[Status] )
                && "End of Run" IN VALUES ( 'Table1'[Status] ),
            1,
            0
        )
    ),
    [IfHit]
)
    HaveBoth =
SUMX (
    SUMMARIZE (
        'Table1',
        'Table1'[Lot Number],
        "IfHit", IF (
            "Start of Run" IN VALUES ( 'Table1'[Status] )
                && "End of Run" IN VALUES ( 'Table1'[Status] ),
            1,
            0
        )
    ),
    [IfHit]
)

    Finally you could get the results. Note, pay attention to the fields in the report please. odd result my appear.

     

     

     

     

     

     

     

     

     

     

     

     

     

     

     

    Best Regards!

    Dale

6 Replies

  • sarogersz's avatar
    sarogersz
    Regular Visitor
    9 years ago

    Working in PBI Desktop if that isn't implicit here....

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      I'll give you one measure that should show you the pattern the rest should take.

       

      End of Run without Start of Run = CALCULATE(
	COUNT(TableName[LotNumber]),
	FILTER(
		TableName,
		ISBLANK(TableName[StartOfRun]) &&
		NOT(ISBLANK(TableName[EndOfRun]))
	)
)

       

      I'm willing to bet you can figure out the next one from here.

      • sarogersz's avatar
        sarogersz
        Regular Visitor
        9 years ago

        The status values are not in separate columns - they are distinct values within a single Status column.  I.e. TableName[Status] = "Start of Run"  OR TableName[Status] = "End of Run".  Each new row in the table is a log entry of Status and other related numerics associated with the status.

         

        I think this solution is close but I'm not sure how to adapt it to the single column status for multiple values across 'n' Lot entries.