Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX that will Count correctly

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])
    )
)

 

 

 

 

Need Help

5 Replies

  • vanessafvg's avatar
    vanessafvg
    Community Champion
    5 years ago
    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's avatar
      Anonymous
      Not applicable
      5 years ago

      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's avatar
        vanessafvg
        Community Champion
        5 years ago

        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's avatar
      Anonymous
      Not applicable
      5 years ago

      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