Forum Discussion

saurabhtd's avatar
saurabhtd
Icon for Resolver II rankResolver II
3 years ago
Solved

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 ?

  • tamerj1's avatar
    tamerj1
    3 years ago

    Hi saurabhtd 
    Please try

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

10 Replies

    • saurabhtd's avatar
      saurabhtd
      Icon for Resolver II rankResolver II
      3 years ago

      Then calculate DISTINCTCOUNT of customer code

  • tamerj1's avatar
    tamerj1
    Icon for Community Champion rankCommunity Champion
    3 years ago

    Hi saurabhtd 
    Please try

    =
VAR SourceN =
    SWITCH (
        SELECTEDVALUE ( TableName[CountryName] ),
        "A", "X",
        "B", "X",
        "C", "Z"
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( TableName[customercode] ),
        TableName[SourceName] = SourceN
    )
    • saurabhtd's avatar
      saurabhtd
      Icon for Resolver II rankResolver II
      3 years ago

      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.  

      • tamerj1's avatar
        tamerj1
        Icon for Community Champion rankCommunity Champion
        3 years ago

        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
    )
  • Anonymous's avatar
    Anonymous
    Not applicable
    3 years ago

    Hi saurabhtd ,

     

    Has the problem be solved?

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