Forum Discussion

Oros's avatar
Oros
Post Prodigy
2 years ago
Solved

DistincCount with Filter

Hello,   I have a measure that works using DISTINCOUNTNOBLANK. How do you add a filter to this measure, for example, ONLY distinct count "the product number that starts in number 1 and 2"? Thanks...
Need Help
  • speedramps's avatar
    speedramps
    2 years ago

    The solution will works. Please try copy it carefully. Then click accept solution. Thanks.

    Answer =
    VAR tempfile = FILTER('yourtable',NOT LEFT('yourtable'[PRODUCT NUMBER],1) IN {"2","3"})
    RETURN
    CALCULATE(
    DISTINCTCOUNT('yourtable'[PRODUCT NUMBER]),
    tempfile)

     

     

     

Oros's avatar
Oros
Post Prodigy
2 years ago

Hi speedramps,

 

Thank you for your comment.

 

I have a sample table with only one column like this.  What measure to use to count the number of distinct products, EXCLUDING products that start in number 2 and number 3?  Thanks again.

PRODUCT NUMBER
10023
10024
10025
20018
20019
20020
30009
30010
Daniel_PowerBI's avatar
Daniel_PowerBI
Advocate I
2 years ago

That should do it:

Distinct count =
CALCULATE(DISTINCTCOUNT('Table'[PRODUCT NUMBER]),LEFT('Table'[PRODUCT NUMBER]) <>"2" && LEFT('Table'[PRODUCT NUMBER]) <>"3")