DAX that will Count correctly

[Anonymous]

Hello,

I have the following Dax's:

<100K = [Transfer Payments Total]-[100k+]

100k+ = 
SUMX (
    FILTER (
        FILTER (
            SUMMARIZE (
                'ZZPAR2 III-9A Transfer Payments',
                '2_QR-Fund Report'[Recipient Class (English)],
                '3_Tax-Vendor Number'[Recipient Name (English)],
                "100k", SUM ('ZZPAR2 III-9A Transfer Payments'[Amount] )
            ),
            NOT ( ISBLANK ( '3_Tax-Vendor Number'[Recipient Name (English)] ) )
        ),
        [100k] >= 100000
    ),
    [100k]
)

Transfer Payments Total = 
SUMX(
    'ZZPAR2 III-9A Transfer Payments','ZZPAR2 III-9A Transfer Payments'[Amount]
)

I am trying to perform a name count with the following Dax but it is counting blank names for a total of 18 instead of 2. What must I add?

NameCount4 = 
IF([<100K]<>0,
    CALCULATE(
    DISTINCTCOUNT('ZZPAR2 III-9A Transfer Payments'[Name])
    )
)

[vanessafvg]

NameCount4 = 
IF([<100K]>0,
    CALCULATE(
    DISTINCTCOUNT('ZZPAR2 III-9A Transfer Payments'[Name])
    )
)

have you tried > 0 ? 

can you provide sample data in text form?

[Anonymous]

@vanessafvg Hello Vanessa,

Here is the link to a similar model and database with the same Dax: https://1drv.ms/u/s!Aux8yCChQ6opu3MONtYpvmQ_2-P4?e=Cf3frC 

The first tab of the report gives you a table for my Database and Lookup Tables and you can see how the relationship is built. The tab also contains the correct answer for my Class for Companies that have 100,000 or more of product which is my 100k+ Dax and I concluded that for my Less than 100K I would simply use the Database Total - 100K+. ALthough that Dax gives me the correct result I am unable to obtain a proper Count of the items that make up the answer.  I believe I either need a specifi Dax for my Less than 100k or I need to perform some filter clearing which I am not an expert at doing. 

Any help is appreciated

[vanessafvg]

Hi I have had a look at your pbix and I have to say I was a little confused in your dax that you shared you were counting names, now you say counting items?   

Besides that though, what i realised was what you are actually asking is about the total?  so the measure works correctly howevever the total is wrong, please just clarify that that is what you are asking?

In the dax you provided it is very thorough, however because it is slighly different to what you presented here, I need to make sure I understand what you are asking?

ie would this be the same dax query?

NameCount4 =

IF([100k-]<>0,

CALCULATE(

DISTINCTCOUNT(CompanyNameLookup[Correct Company Name])

)

)

is the seven presented here what you are referring to?

[Anonymous]

Hello @vanessafvg ,

That is correct under my 100K- Dax when I try to obtain the correct name count I do not obtain the proper count since it is also counting the blanks. Yes the NameCount4 was my initial failed attempt at obtaining the correct Dax Count. Sorry for the confusion yes it is a name count I am looking for but I added an index count to the table just to see if that would make the calculation any easier but I guess not. Sorry for the late reply! 

[Anonymous]

Yes I have and 18 still shows up in the total although the sum of each rows only shows 1 and 1 like in my image. Unfortunately my model has over 231,653 lines of data with sensitive information which I cannot share. I would have to replicate the issue in a similar model. I even tried with DISTINCTCOUNTNOBLANK and I get the same results