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

Join us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.

Reply
Anonymous
Not applicable

Need help writing a DAX to count distinct values with multiple filters

I have this table

 

thorton_0-1652713895001.png

I need to write a DAX that finds Admit Status of 'N' or 'E'

Then it need to modify FAYEAR to get the left two characters and change that to 202110

So it would be "20" & Left(FAYEAR,2) & "10"  newvalue

 

So then it would (Admit Status of 'N' or 'E') and "20" & Left(FAYEAR,2) & "10"  newvalue

 

I wrote this but I am doing something wrong.

THANK YOU!

 

H21 = CALCULATE(DISTINCTCOUNT('Main'[ID]), FILTER('Main',Main[ADMITSTATUS] = "N" || 'Main'[ADMITSTATUS] = "E" && Main[ADMITTERM] = "20" & LEFT(CALCULATE(DISTINCT(H2A_FR[FAYEAR])), 2)& "10"))
1 ACCEPTED SOLUTION

Hi @Anonymous ,

 

In the formula, "ADMITTERM" uses the same name as the Main[ADMITTERM] field, you need to replace it, such as "ADMITTERM 2".

 

vkkfmsft_0-1653010852396.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.


Best Regards,
Winniz

View solution in original post

5 REPLIES 5
Greg_Deckler
Super User
Super User

@Anonymous Perhaps:

H21 = 
  COUNTROWS(
    DISTINCT(
      ADDCOLUMNS(
       FILTER('Main',Main[ADMITSTATUS] = "N" || 'Main'[ADMITSTATUS] = "E"),
        "ADMITTERM", "20" & LEFT(CALCULATE(DISTINCT(H2A_FR[FAYEAR])), 2)& "10"
      )
    )
  )


Follow on LinkedIn
@ me in replies or I'll lose your thread!!!
Instead of a Kudo, please vote for this idea
Become an expert!: Enterprise DNA
External Tools: MSHGQM
YouTube Channel!: Microsoft Hates Greg
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
Anonymous
Not applicable

Error that it cannot add columns because already exists.

 

thorton_0-1652717525946.png

 

Hi @Anonymous ,

 

In the formula, "ADMITTERM" uses the same name as the Main[ADMITTERM] field, you need to replace it, such as "ADMITTERM 2".

 

vkkfmsft_0-1653010852396.png

 

If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.


Best Regards,
Winniz

Anonymous
Not applicable

Thank you!

Anonymous
Not applicable

I think I figured it out, 

H21 = CALCULATE(DISTINCTCOUNT('Main'[ID]), FILTER('Main',Main[ADMITSTATUS] in {"N","E"} && Main[ADMITTERM] = "20" & LEFT(CALCULATE(DISTINCT(H2A_FR[FAYEAR])), 2)& "10"))
 
But my question still remains what is I had to combine filters
 
(Filter A = "b" or Filter B = "b") and Filter C = "x"
 
How would you write the above filter in DAX?
 

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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