Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
RK9009
Frequent Visitor

Dax Help

Greeting PBI Community, I need help with this dax measure

studentIDstatus
abc123status 1 
abc123status 2
abc123status 3
abc123status 4
bca231status 2
bca231status 3
cab321status 2
cab231status 3
xyz234status 1 
alm222status 2
blz121status 3
cla234status 3
zla232status4
cls343status4

 

 

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 

8 REPLIES 8
Fowmy
Super User
Super User

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

RK9009
Frequent Visitor

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

@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

 

Fowmy_0-1599681940675.png

 

________________________

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

 

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

RK9009
Frequent Visitor

@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

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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

RK9009
Frequent Visitor

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.

 

 

@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

Did I answer your question? Mark my post as a solution! and hit thumbs up


Subscribe and learn Power BI from these videos

Website LinkedIn PBI User Group

RK9009
Frequent Visitor

Sorry Status 2 =2 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors