Possible Group By required - how?

[Anonymous]

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.

[v-juanli-msft]

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]

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]

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 · Accepted solution]

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.