Dax Help

Question

Greeting PBI Community, I need help with this dax measure

studentID	status
abc123	status 1
abc123	status 2
abc123	status 3
abc123	status 4
bca231	status 2
bca231	status 3
cab321	status 2
cab231	status 3
xyz234	status 1
alm222	status 2
blz121	status 3
cla234	status 3
zla232	status4
cls343	status4

here’s an example: first we distinctcount all the studentIDs in Status1 - I.e 2
then we count all student IDs in Status 2 exluding the ones counted in Status1
then count all Student IDs in Status3 exlucding IDs in Status1&2
Then Count all IDs in status4 excluding IDs counted in Status 1,2,3

Thank you

fowmy · Answer

RK9009&nbsp;Do you need a single measure that calculates one result or is against each Status?Can you share the expected output as well?________________________If my answer was helpful, please consider Accept it as the solution to help the other members find itClick on the Thumbs-Up icon if you like this reply 🙂YouTube&nbsp; LinkedIn

rk9009 · Answer

It's ok if we use a measure per status:starting with: distinct status1=calc(countdistinct(studentID),Status="status 1")Expected Result :Status1 = 2Status2 = 3Status3 = 2Status4 = 2

rk9009 · Answer

Sorry Status 2 =2&nbsp;

fowmy · Answer

RK9009&nbsp;Please recheck your results: Following Measure works for me:Total Count = 
VAR DT1 = 
CALCULATETABLE(
    DISTINCT('Table1 (2)'[studentID]),
    FILTER(ALL('Table1 (2)'[status]), 'Table1 (2)'[status] &lt; SELECTEDVALUE('Table1 (2)'[status]))
)

VAR DT2 = 
CALCULATETABLE(
    DISTINCT('Table1 (2)'[studentID]),
    FILTER(ALL('Table1 (2)'[studentID]),
        NOT 'Table1 (2)'[studentID] IN DT1 ) 
)
VAR D2 =  COUNTROWS(DT2)

RETURN

D2&nbsp;&nbsp;________________________If my answer was helpful, please consider Accept it as the solution to help the other members find itClick on the Thumbs-Up icon if you like this reply 🙂YouTube&nbsp; LinkedIn&nbsp;

rk9009 · Answer

Fowmy&nbsp;thank you so much for the reply, can you please specify what the status numbers are in the measure;&nbsp;Total Count = 
VAR DT1 = 
CALCULATETABLE(
    DISTINCT('Table1 (2)'[studentID]),
    FILTER(ALL('Table1 (2)'[status]), 'Table1 (2)'[status] &lt; SELECTEDVALUE('Table1 (2)'[status]))
)

VAR DT2 = 
CALCULATETABLE(
    DISTINCT('Table1 (2)'[studentID]),
    FILTER(ALL('Table1 (2)'[studentID]),
        NOT 'Table1 (2)'[studentID] IN DT1 ) 
)
VAR D2 =  COUNTROWS(DT2)

RETURN

D2

Forum Discussion

Dax Help

8 Replies

Related content

Dax

DAX

DAX Behavior

DAX Formula Help

DAX