Forum Discussion

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 ...

Need Help

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

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

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.

v-jiascu-msft

Microsoft Employee

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

v-jiascu-msft
Microsoft Employee
9 years ago
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
  Regular Visitor
  9 years ago
  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.