count distinct filtering with multiple values

[luisccmm]

Hi,

I am new to Power BI, but used to Qlik and a bit confused at the moment.

1)Question 1: I am trying to" count Clients_id that their Client_type do not belong to Level1, Level2, Level3 and Level 4.

MY  DAX ATTEMPT:

client_id_count=

CALCULATE
(
    count('SALES table'[Client_id]),
     NOT [Client_type]  IN {"LEVEL1","LEVEL2","LEVEL3","LEVEL4"}
) 

2) Question 2: On the other hand I would like to know how to mimic "Contains concept using maybe some string as '*' ". I was used to do it that way in Qlik, but not sure how it is in PowerBI. I mean something like  "*1",*2",*3",*4".

NOTE: Client Type  distinct values dimensions are close 30. (Level 1.... Level 30)

client_id_count=
CALCULATE
(
    count('SALES table'[Client_id]),
     NOT [Client_type]  IN {"*1","*2","*3","*3"}
) 

Any help will be appreciated!😀

[edhans]

@luisccmm it would really help if you posted some sample data. @mahoneypat and I are kind of guessing how your data looks, and you just keep coming back with "it doesn't work."

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[luisccmm]

@edhans here is the sample data.

https://drive.google.com/file/d/1pBuOkxcYkjZF7woe2GVyc0aN93QWN9_y/view?usp=sharing

[edhans · Accepted solution]

This measure works @luisccmm

client_id_count = 
CALCULATE(
    COUNTROWS(Hoja1),
    FILTER(
        VALUES(Hoja1[Client_type]),
        NOT( Hoja1[Client_type] IN {"LEVEL1","LEVEL2","LEVEL3","LEVEL4"})
        )
)

[luisccmm]

@edhans to be more precise still part of the question is unsolved because I want to SAY exactly that Client_type DOES NOT ENDS in 1, 2, 3 and 4. This is my real propose thats why my attemps was something like the following.

client_id_count=
CALCULATE
(
    count('SALES table'[Client_id]),
     NOT [Client_type]  IN {"*1","*2","*3","*3"}
) 

[mahoneypat]

Here is one way to do it.

Client id count =
CALCULATE (
    COUNT ( 'Sales Table'[Client_id] ),
    FILTER (
        ALL ( 'Sales Table'[Client_type] ),
        NOT (
            SEARCH (
                "Level",
                'Sales Table'[Client_type],
                ,
                0
            ) > 0
        )
    )
)

Regards,

Pat

[luisccmm]

@mahoneypat   thanks for your proposal, but there are 30 distinct values on Client Type dimension that DO include "Level" word, I am just trying to NOT include  Leve 1-  Level2 - Level 3 and Level4 ON my Count Measure.

[mahoneypat]

Note that you can use wildcards in the one I provided (* for multi and ? for single character).  But here is another expression that may be what you are looking for.  It also allows the use of wildcards.

Client id count 2 =
CALCULATE (
    COUNT ( 'Sales Table'[Client_id] ),
    FILTER (
        ALL ( 'Sales Table'[Client_type] ),
        NOT (
            VAR thisclient = 'Sales Table'[Client_type]
            RETURN
                COUNTROWS (
                    FILTER (
                        {
                            "*1",
                            "*2"
                        },
                        CONTAINSSTRING (
                            thisclient,
                            [Value]
                        )
                    )
                ) > 0
        )
    )
)

Regards,

Pat

[edhans]

Did your dax attempt work?

How to get good help fast. Help us help you.
How to Get Your Question Answered Quickly - Give us a good and concise explanation
How to provide sample data in the Power BI Forum - Provide data in a table format per the link. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.

[luisccmm]

@edhans No ti didn´t work.