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

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.

 

v-jiascu-msft's avatar
v-jiascu-msft
Icon for Microsoft Employee rankMicrosoft 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