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

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.

       

      • 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