Forum Discussion

trying's avatar
trying
Frequent Visitor
4 years ago
Solved

Improving Performance Filter Measure Dax

Hi there,

 

i am pretty new to dax and hope for your help with the following measure:

 

Test =
VAR no1 =
    CALCULATETABLE ( VALUES (Orders_Facts[Customer No. ] ) )

VAR no2 =
    FILTER ( no1, [Filter] <> 0 )
RETURN
    COUNTROWS ( no2 )

 

The base for the first measure is the following:

 

Filter =
CALCULATE (
    SUM ( Sales_SCA[Calc. net-net turnover] ),
    Sales_SCA[AC_PL] = "Ist",
    TREATAS ( VALUES ( Orders_Facts[Item No.] ), Sales_SCA[Item No] ),
    TREATAS ( VALUES ( Orders_Facts[Customer No.] ), Sales_SCA[Customer No] )
)

 

the problem is the filter i assume, but i havent found an other way to write it which delivers the correct result.

 

Thank you in advance for your efforts and KR,

KO

Need Help
  • V-lianl-msft's avatar
    V-lianl-msft
    4 years ago

    Hi trying ,


    Please try:

    Test =
COUNTROWS (FILTER(VALUES(Orders_Facts[Customer No. ] ),ABS([Filter])>0))

     

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

3 Replies

  • PaulDBrown's avatar
    PaulDBrown
    Community Champion
    4 years ago

    You can simplify Test with:

    test =
VAR no2 =
    FILTER ( VALUES ( Orders_Facts[Customer No. ] ), [Filter] <> 0 )
RETURN
    COUNTROWS ( no2 )

     

    How is the model set up?

  • V-lianl-msft's avatar
    V-lianl-msft
    Community Support
    4 years ago

    Hi trying ,


    Please try:

    Test =
COUNTROWS (FILTER(VALUES(Orders_Facts[Customer No. ] ),ABS([Filter])>0))

     

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