Requirement: I have a table called Transactions which has 3 columns ID, [Billing Type] and [Year-Month] as mentioned below. I need to find the distinct count of ID by [Year-Month] where [Billing Type] = "Credit Card" and the distinct count of ID >= 3.
Transactions Table (sample data):
ID Billing Type Year-Month
| 1 | Online | 2017-01 |
| 2 | Credit Card | 2017-01 |
| 2 | Credit Card | 2017-01 |
| 3 | Credit Card | 2017-01 |
| 4 | Credit Card | 2017-01 |
| 5 | Credit Card | 2017-01 |
| 6 | Credit Card | 2017-02 |
| 6 | Credit Card | 2017-02 |
| 7 | Credit Card | 2017-02 |
| 8 | Credit Card | 2017-02 |
| 9 | Credit Card | 2017-02 |
| 10 | Credit Card | 2017-02 |
| 1 | Credit Card | 2017-03 |
| 1 | Online | 2017-03 |
| 2 | Credit Card | 2017-03 |
| 2 | Credit Card | 2017-03 |
| 3 | Credit Card | 2017-03 |
| 4 | Credit Card | 2017-03 |
| 4 | Credit Card | 2017-04 |
| 4 | Credit Card | 2017-04 |
| 14 | Online | 2017-04 |
| 12 | Online | 2017-04 |
| 22 | Credit Card | 2017-05 |
| 34 | Credit Card | 2017-06 |
| 45 | Online | 2017-06 |
Select * From(Select distinct Count([ID]) as [RowCount] ,[Year-Month] from Transactions where [Billing Type] = 'Credit Card' GROUP BY [Year-Month] ) as Base Where Base.[RowCount] >= 3
When I run the above SQL code I get the following output.
Expected Output:
RowCount Year-Month
Now I need to get the same result using DAX. Can someone please help me to get the same using DAX?
