Translation of a SQL Query Into DAX to create a Calculated Column in PowerPivot

Question

Hi I am building a PowerPivot Data Model Using "Person" table which has the columns "Name" and "Amount"

Table - Person

|Name | Amount|

|Red | 10|

|Blue | 10|

|Red | 16|

|Blue | 82|

|Red | 82|

|Red | 54|

|Red | 61|

|Blue | 82|

|Blue | 82|

The Output is as expected :

---

| Name | Amount | Count(Specific_Amount) |

| Red |10 | 2 |

| Blue | 10 | 1 |

|Red | 16 | 1|

|Blue | 82 | 3|

|Red |82 | 1|

|Red | 54 | 1|

|Red | 61 | 1|

What i Have Tried till now is :

select Name, distinct Amount, count(Amount) as CountOfAmountRepeated 
from Person 
group by Amount
order by Amount;

I have imported my table "Person" into PowerPivot in Excel. I want to create a Calculated Column In PowerPivot in Excel to create a new column of count of Repeated Amount Values. i was able to do this in SQL by using the above query, But i wanted an Equivalent DAX query for creating a new column in PowerPivot. Can someone translate this query into DAX or say a tool to translate sql into DAX so that i can create an Calculated column and Use PowerView to prepare a histogram of this data. tried googling but no much help. Advance Thanks ..

Answer 1

There are a lot of facets of you question that need to be addressed but very simply (without consideration of any other requirements) the calculation is:

Count(Specific_Amount):=COUNTROWS('Person')

*All you seem to be looking to do here is count the unique instances of each combination.

If you then then created a pivot table dragging the [name] and [amount] into the rows and [Count(Specific_Amount)] into the values you would have the answer you are looking for, To get the layout you want you could change the layout to tabular form and remove the sub totals.