Dax Help

[RK9009]

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]

@RK9009 

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 it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[RK9009]

It's ok if we use a measure per status:
starting with: distinct status1=calc(countdistinct(studentID),Status="status 1")

Expected Result :
Status1 = 2
Status2 = 3
Status3 = 2
Status4 = 2

[Fowmy]

@RK9009 

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] < 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

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[RK9009]

@Fowmy thank you so much for the reply, can you please specify what the status numbers are in the measure;

Total Count = 
VAR DT1 = 
CALCULATETABLE(
    DISTINCT('Table1 (2)'[studentID]),
    FILTER(ALL('Table1 (2)'[status]), 'Table1 (2)'[status] < 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

[Fowmy]

@RK9009 

No need to specify, when you drop this measure against the status in a table visual as I did, it calculates based on the status value. The status1 is lower than status2 and so on. You can add more status values  in the same order if needed. 

let me know if you need any help after the testing  it. 

[RK9009]

Does it work if it is a has to follow a hierarchy as in 

All id in A being first to all ID in D.

[Fowmy]

@RK9009 

I did not fully understand your question, why don't you try,

as per your sample, it works 

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn

[RK9009]

Sorry Status 2 =2