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               val

4            7               valid

5            7               start

6            8               valid

 

The relationship is Table A.id JOIN Table B.a-id (one to many).

 

I would like to count the rows in Table A (counting on this Table as it's the one side of the relationship) where all statuses in Table B for the a-id have soley 'val' or 'valid' values.

So from the above example the count should equal 1 because it is only TableA id value 8 that has soley a 'val' or 'valid' for the status.

 

I suspect a GROUP BY is required (could be wrong). How do I acheive the required in DAX?


Thanks.

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.

4 Replies

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