Filter to check my value is of integer type

[Ethanhunt123]

I want to distinct count my account number where depositor name is in an integer format.

[harshnathani]

Hi @Ethanhunt123 ,

You can check ISNONTEXT(), ISNUMBER(), NOT(ISTEXT())

https://dax.guide/isnumber/

https://dax.guide/isnontext/

https://dax.guide/istext/

https://dax.guide/not

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[parry2k]

@Ethanhunt123 without looking at the data very hard to say what you are trying to do but here is a measure

Unique Count = DISTINCTCOUNT ( Table[Column] )

I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!

⚡Visit us at https://perytus.com, your one-stop shop for Power BI related projects/training/consultancy.⚡

[Ethanhunt123]

I know about the distinct count. I am stuck in the filter part. 

Dax = DistinctCount(Table[Column]), filter(table,table[Column1] = (Count when my column1 is an integer)

[harshnathani]

Hi @Ethanhunt123 ,

Not sure what you mean.

Does COLUMN1 contain multiple formats ie does a single column contain text, integers ??

Please share sample data to help you.

Regards,

HN

[Ethanhunt123]

Account number	Name
12345	Steven
2345	Steven1
87921	123

Output - 1

[harshnathani · Accepted solution]

Hi @Ethanhunt123 ,

You can do it with a combination of Power Query and DAX.

In Power Query

add a custom column and use this formula

= Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type text) then _ else null))))

Then Change the type to Text

You can then use a DAX Measure

counti =

CALCULATE(DISTINCTCOUNT('Table'[Account number]),FILTER('Table','Table'[Custom] = BLANK()))

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

[dax]

Hi @Ethanhunt123 , 

You could  refer to my sample for details.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WMjQyNjFV0lEKLkktS81TitWJVkIRMQQLWZhbGhkCxYCqoUoMjUH84mJDmBYgMgapMDUxNTFTio0FAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Account number" = _t, Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Account number", Int64.Type}, {"Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Custom", each Text.Combine(List.RemoveNulls(List.Transform(Text.ToList([Name]),each if Value.Is(Value.FromText(_), type number) then _ else null))))
in
    #"Added Custom"

Best Regards,
Zoe Zhi

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

[Ethanhunt123]

It contains alphanumeric, string, integer all type f value. But I want to calculate only for the integer value.