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

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
saurabhtd
Resolver II
Resolver II

Need help in DAX Logic

I want to do a calculation of DISTINCTCOUNT(TableName[customercode]) of customers from all three countries. But when I select country from countryname filter these conditions should be satisfied.

when countryname = "A" I need to take data from sourcename = "X"
when countryname = "B" I need to take data from sourcename = "X"
when countryname = "C" I need to take data from sourcename = "Z"

There are two filters applied sourcename and countryname.

Note : Customercode,countryname,sourcename are from same table.

I am unable to write required dax to fulfill these conditions. Can someone please help ?

1 ACCEPTED SOLUTION
tamerj1
Super User
Super User

Hi @saurabhtd 
Please try

=
VAR SourceN =
    SWITCH (
        SELECTEDVALUE ( TableName[CountryName] ),
        "A", "X",
        "B", "X",
        "C", "Z"
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( TableName[customercode] ),
        TableName[SourceName] = SourceN
    )

View solution in original post

10 REPLIES 10
Anonymous
Not applicable

Hi @saurabhtd ,

 

Has the problem be solved?

Please consider to mark the reply as solution if it's helpful.

 

tamerj1
Super User
Super User

Hi @saurabhtd 
Please try

=
VAR SourceN =
    SWITCH (
        SELECTEDVALUE ( TableName[CountryName] ),
        "A", "X",
        "B", "X",
        "C", "Z"
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( TableName[customercode] ),
        TableName[SourceName] = SourceN
    )

Thanks for replying. Now when I select particular country in filter I am getting required answer. But how to get deafult condition which is  to calculate distinctcount(customer code) when all the countries 'A','B','C' are selected in the filter.  I am strucked at this point now.  

@saurabhtd 
I believe this should be fully dynamic therefore no need for default value.

=
VAR SourceN =
    SELECTCOLUMNS (
        VALUES ( TableName[CountryName] ),
        "@Country", SWITCH ( TableName[CountryName], "A", "X", "B", "X", "C", "Z" )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( TableName[customercode] ),
        TableName[SourceName] IN SourceN
    )

@tamerj1 Thank you again for answering. This is not working. It is giving answer only when country name = "B" and even that answer to is wrong.  

@saurabhtd 
Would you please share a screenshot of your visual?

@tamerj1 I need to used it in a card in report of dashboard. Currently I am verfying on new page.  what is purpose of "@country" before switch is ? I have not understood this .Screenshot 2022-11-29 170351.png

@saurabhtd 
It would be really helpful if you could share a sample file to work with. You can share a download link or send to my email. tamer_juma@yahoo.com

FreemanZ
Super User
Super User

take data from sourcename = "X", then what?

Then calculate DISTINCTCOUNT of customer code

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

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

July PBI25 Carousel

Power BI Monthly Update - July 2025

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