(DAX) Problem - Data Profiling Measures

[sarogersz]

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

[sarogersz]

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

[KHorseman]

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]

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.

[v-jiascu-msft]

Hi @sarogersz,

Could you please mark the proper answer as solution or share the solution if it's convenient for you? That will be a big help to the others.

Best Regards!
Dale

[sarogersz]

Yes I will share some key aspects of the solution going forward.  I think there is good pay dirt here for helping solve data quality issues using PBI.  I'll chime in as we take the solution to the customer.

[v-jiascu-msft · Accepted solution]

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