DistincCount with Filter

[Oros]

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.

TOTAL ITEMS = DISTINCTCOUNTNOBLANK(Product_Table[product number])

[parry2k]

@speedramps great advice and I also have a small advice for you. I hope don't mind. When you paste a DAX or M code, instead of pasting it as text, use the insert/edit code sample button on the editor and paste the code there. It differentiates the code from the rest of the text/message.

[parry2k]

@Oros the syntax seems to be correct, can you share the expression you are using?

[speedramps]

Try this ..

Answer =

VAR felixfile = FILTER('yourtable',NOT LEFT('yourtable'[PRODUCT NUMBER],1) IN {"2","3"})

RETURN

CALCULATE(

DISTINCTCOUNT('yourtable'[PRODUCT NUMBER]),

felixfile)

Thanks for the clear description of the problem with example data. I wish everyone did that!

Remember we are unpaid volunteers. So please click the thumbs up and the [accept as solution] button to leave kudos. 

If you give someone a fish then you only give them one meal, but if you teach them how to fish then they can feed themselves and teach others for a lifetime.  This is how I try answer Power BI and DAX questions with clear examples so you learn techniques.

One question per ticket please. If you need to extend your request then please raise a new ticket.

You will get a quicker response and each volunteer solver will get the kudos they deserve. Thank you ! 

If you quote @speedramos in your next tickets then I will then receive an automatic notification, and will be delighted to help you again.

Please click the thumbs up and the [accept as solution] button. 

[Oros]

Hi @speedramps ,

I am getting an error "the syntax for 'RETURN' is incorrect.

[speedramps · Accepted solution]

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]

Hi @speedramps ,

Thanks!!!

[speedramps]

Thanks @Oros for accepting the solution.

Please quote @speedramps in any future questions and I will will receive an automated notification and try resolve it.
Always try provide example input data, desired output and clear step by step description. Provide input date as a table and not a picture so we can import it and build a soluton. Thanks

[speedramps]

We want to help you but your description is too vaugue. Please write it again clearly.

Please DON'T copy & paste your DAX that does not work and expect us to fathom what you want to do. That would be crazy. 😀

Please just give a simple non technical functional decscription of what you want, then let us suggest the DAX. Thank you.

Provide example input data as table text (not a screen print) so we can import the data to build a solution for you.
Also provide the example desired output, with a clear description of the process flow.

Remember not to share private data ... we don't want you to get into trouble. 😧

Take time and care to use the same table and field names in the input, output and description so we can understand your problem and help you.

You will get a quick response if you put time, care and effort into writing clear problem descriptions.

Vaugue descriptions can waste your time and ourtime.

Look foward to helping you when the above information is forthcoming

[Oros]

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]

That should do it:

Distinct count =

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