Forum Discussion

Thim's avatar
Thim
Resolver V
7 years ago
Solved

Count.If from the same table

Hi All.   I have a general issue i hope you can help me with.   I have searched alot on how to make a Count.If function Using DAX.   So far i have found a way, if i am using two tables. As an ...
Need Help
Tips and Tricks
  • v-cherch-msft's avatar
    v-cherch-msft
    7 years ago

    Hi Thim

     

    I think maybe it's better to do that in Query Editor when the data is large. Here is the sample file for your reference.

     

    Regards,

    Cherie

Thim's avatar
Thim
Resolver V
7 years ago

Here is a small sample data.

https://www.dropbox.com/s/sqimlnnzkvlgzz7/Sample.pbix?dl=0

 

The colums are as follow:

Costumer: the number of the costumer

Bill to Costumer: who is billed for this costumers order.

Number of bill to (Expected results): the cumber of other costumers who is billed to this costumer

Number of bill to: the DAX Functions i use, but dosnt give me the expectet result (This will work if i dublicate the table and make the DAX Functions combining those tables)

 

Hope you guys have a solution. :-)

 

I would prefer to avoid the EARLIER function as this is a very slow function if you have lots of lines.

In this particular situation it might work, but if i need to do this with 100.000+ lines, it will usually slow down Power BI to much.

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

I can only solve it with the EARLIER() function.  This calculated column formula works

 

Number og bill to = CALCULATE(COUNT('Costumer Table'[Bill to Costumer]),FILTER('Costumer Table','Costumer Table'[Bill to Costumer]=EARLIER('Costumer Table'[Costumer])))