Forum Discussion

isyammil's avatar
isyammil
Frequent Visitor
5 years ago

Total measure is wrong using variable with distinctcount and filter

Hello,

 

Im using simple DAX which use Variable to store calculation then return distinctcount with filter using the variable.

But the total shows only the distinctcount correct value which 5. But it should show 4 after filter method. I dont know what part is wrong. Please see my DAX and table below. Thanks so much for the help.

 

Buying Customer =

var x = CALCULATE(SUM('combineSales'[Value]),PARALLELPERIOD(LASTDATE('Calendar'[Date]),0,MONTH))
 
RETURN
 
CALCULATE(DISTINCTCOUNT(combineSales[IM Customer Code]),FILTER(combineSales,x>=200))
 
IM Customer CodeSum T0 ValueBuying Customer
11500
23001
34001
45001
56001
Total19505
Need Help

3 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    isyammil , Try like

     

    Buying Customer =

    var x = CALCULATE(SUM('combineSales'[Value]),PARALLELPERIOD(LASTDATE('Calendar'[Date]),0,MONTH))

    RETURN

    CALCULATE(DISTINCTCOUNT(combineSales[IM Customer Code]),FILTER(values(combineSales[IM Customer Code]),combineSales,x>=200))

     

     

    Or

     


    Buying Customer =

    var x = CALCULATE(SUM('combineSales'[Value]),PARALLELPERIOD(LASTDATE('Calendar'[Date]),0,MONTH))

    RETURN

    CALCULATE(countx(FILTER(values(combineSales[IM Customer Code]),combineSales,x>=200)),[IM Customer Code])

    • isyammil's avatar
      isyammil
      Frequent Visitor
      5 years ago

      Thanks for help, but your first method gave same result while second method return error cannot using COUNTX

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    5 years ago

    Hi isyammil ,

     

    Need to count the Buying customer field instead of the IM Customer Code field. So try the following formula:

    Buying Customer =
VAR x =
    CALCULATE (
        SUM ( 'combineSales'[Value] ),
        PARALLELPERIOD ( LASTDATE ( 'Calendar'[Date] ), 0, MONTH )
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( combineSales[Buying Customer] ),
        FILTER ( combineSales, x >= 200 )
    )

     

    If the problem is still not resolved, please provide detailed error information and let me know immediately. Looking forward to your reply.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.