Count.If from the same table

[Thim]

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 example, this way would look like this.

Count Bill to Costumer = CALCULATE(COUNT('Customer'[Bill-to Customer No_]);
    FILTER(ALL('Customer');'Customer'[Bill-to Customer No_]='Customer - To Add Bill To'[No_])
)

By filtering from one table to another, I get my wanted result.

However if i use the same approach but from one table instead of two, i wont get the same result.

It will either count the rows or repeat the first results it gets on all lines.

I can dublicate the table, and get the resultat I want to the new table, and then use lookupvalue to get the correct result back to my orignal table, but this isnt a very smart way, and i would prefer not to dublicate a whole table every time i need to count a value.

Does anyone know how to use DAX to make a Count.If Function using only one table.

Thank you very much. 🙂

Best regards.

Thim

[Thim]

Hi All.

Thanks for all the suggestions and solutions.

At this point it does not look like this can be solved using only DAX without using the earlier function as well.

for now i will note Cheries answer as the solution, as i feel this gives the best solution to my problem.

This still requires two tables, but by mergin the tables, and then "Hide" the second table, does gives a quite nice result.

Thanks again all. 🙂

[Thim]

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]

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

[Ashish_Mathur]

Hi,

Why should the answer against Customer 1 be 8?  how have you computed that number 8?

[v-cherch-msft]

Hi @Ashish_Mathur

I think it's the count of  1 in 'Bill to costumer' column.

Regards,

Cherie

[v-cherch-msft · Accepted solution]

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

[v-cherch-msft]

Hi @Thim

It seems you may try to use SELECTEDVALUE Function or EARLIER Function. If it is not your case, please share some data sample and expected output. You can upload it to OneDrive or Dropbox and post the link here.

Regards,

Cherie

[PattemManohar]

@Thim Please post the sample data and your expected output