Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Possible Group By required - how?

Hi, I have the following tables: Table A id 6 7 8   Table B id           a-id           status 1            6               val 2            6               end 3            7          ...
Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    Okay, so I've resolved it myself.

    So to help others, here is what I did...

     

    1) In Power Query, I created a conditional column and assigned 1 to every status value that was not 'validated' or 'validated'.

    2) In DAX, I wrote:

    tblScriptPacketsCompleted = FILTER(
    SUMMARIZE(
    refSCRIPT_VALIDATION,
    refSCRIPT_VALIDATION[packet_id],
    "Values", SUM(refSCRIPT_VALIDATION[ValidatedORValidated_Rejected])
    ),
    [Values] = 0
    )

    3) mScriptPacketsCompleted = COUNT( tblScriptPacketsCompleted[packet_id] )

     

    The measure in Step 3 had the answer I assigned to my card visual. Easy isn't it... when you know how ;-)

    Hope this helps others.

v-juanli-msft's avatar
v-juanli-msft
Community Support
8 years ago

Hi Anonymous

Measures below would help.

Measure = IF(MAX([status])="val"||MAX([status])="valid",1,0)

Measure 2 = CALCULATE(COUNT('Table A'[id]),FILTER(ALL('Table B'),[Measure]=0))

 

Best Regards

Maggie

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    Hi Maggie,

    Thanks for your response. It isn't working as I require but this is because I made a small mistake in my example (my fault and now corrected).

     

    I require a count on Table A[id] only when the status of 'val' or 'valid' is the ONLY status in Table B[status] column.

    In my example:

    a-id 6 has entries of 'val' and 'end'. The Table A[id] should not be counted.

    a-id 7 has entries of 'val', 'valid', and 'start'. The Table A[id] should not be counted.

    a-id 8 has an entry of 'valid' only. The Table A[id] should be counted.

     

    Thanks.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      To help explain my requirements more, I have produced the working SQL to acheive what I'm looking for. See below:

      SELECT p.id
      FROM PACKET AS p
      INNER JOIN SCRIPT_VALIDATION sv
      ON p.id IN
      ( SELECT sv.packet_id
      FROM SCRIPT_VALIDATION AS sv
      GROUP BY sv.packet_id
      HAVING SUM(IIF( status <> 'validated' AND status <> 'validated_rejected', 1, 0)) = 0
      )
      GROUP BY p.id
      ORDER BY p.id

       

      How do I acheive the same in DAX?

      Thanks.

      • Anonymous's avatar
        Anonymous
        Not applicable
        8 years ago

        Okay, so I've resolved it myself.

        So to help others, here is what I did...

         

        1) In Power Query, I created a conditional column and assigned 1 to every status value that was not 'validated' or 'validated'.

        2) In DAX, I wrote:

        tblScriptPacketsCompleted = FILTER(
        SUMMARIZE(
        refSCRIPT_VALIDATION,
        refSCRIPT_VALIDATION[packet_id],
        "Values", SUM(refSCRIPT_VALIDATION[ValidatedORValidated_Rejected])
        ),
        [Values] = 0
        )

        3) mScriptPacketsCompleted = COUNT( tblScriptPacketsCompleted[packet_id] )

         

        The measure in Step 3 had the answer I assigned to my card visual. Easy isn't it... when you know how ;-)

        Hope this helps others.