topic Re: Dax Help in DAX Commands and Tips

Dax Help

RK9009 — Wed, 09 Sep 2020 19:06:35 GMT

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

Re: Dax Help

Fowmy — Wed, 09 Sep 2020 19:26:24 GMT

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

Re: Dax Help

RK9009 — Wed, 09 Sep 2020 19:33:38 GMT

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

Re: Dax Help

RK9009 — Wed, 09 Sep 2020 19:37:41 GMT

Sorry Status 2 =2

Re: Dax Help

Fowmy — Wed, 09 Sep 2020 20:06:46 GMT

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

Re: Dax Help

RK9009 — Wed, 09 Sep 2020 20:30:59 GMT

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

Re: Dax Help

Fowmy — Wed, 09 Sep 2020 22:13:42 GMT

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.

Re: Dax Help

RK9009 — Wed, 09 Sep 2020 22:44:40 GMT

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.

Re: Dax Help

Fowmy — Wed, 09 Sep 2020 22:57:55 GMT

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