DAX calculation with 1/countif

Question

I am totally new to DAX and Power Pivot, so I hope I am as clear as possible with my question.

I am trying to replicate an Excel formula as DAX in the Query Editor. What I have actually is a formula that divides 1 by the count function of the number of orders.

For example, if a person bought an item in order 001, the count function will "count" the number of items within that order =1/(COUNTIF($B$2:$B$6000,B2)). In this case the formula will be 1/1. But if a person bougth two items in order 002, the formula will =1/(COUNTIF($B$2:$B$6000,B3)) and it will find 2 events where order 002 appear. Therefore, the formula will be 1/2= 0.5. So, each item represents half of the order. The same in the case of more items bought for the same order.

What I need is to put that formula into a DAX, but I don't know how to reference a cell in the Query Editor. I guess that the solution is not brute forcing it, but to use an expression that I haven't thought about yet, so I will appreciate your knowledge and solutions to my query.

Thanks for your help!

Sample with formula

Alexis Olson Alexis Olson · Accepted Answer · 2017-12-13T05:12:47

You don't reference individual cells like that with DAX. You work with columns and filters instead.

Assuming your table is named Sales, to do this with DAX you could create a calculated column as follows:

Fraction = 1 / 
     CALCULATE(
         COUNT(Sales[Order]), 
         Sales[Order] = EARLIER(Sales[Order])
      ))

This counts the number of orders where the Order ID matches that of the current row. (The EARLIER function allows you to access the row context for each row.)

DAX calculation with 1/countif

1 Answers