Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

From SQL to DAX

I need the following SQL script in DAX format.

 

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

1Online2017-01
2Credit Card2017-01
2Credit Card2017-01
3Credit Card2017-01
4Credit Card2017-01
5Credit Card2017-01
6Credit Card2017-02
6Credit Card2017-02
7Credit Card2017-02
8Credit Card2017-02
9Credit Card2017-02
10Credit Card2017-02
1Credit Card2017-03
1Online2017-03
2Credit Card2017-03
2Credit Card2017-03
3Credit Card2017-03
4Credit Card2017-03
4Credit Card2017-04
4Credit Card2017-04
14Online2017-04
12Online2017-04
22Credit Card2017-05
34Credit Card2017-06
45Online2017-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

42017-01
52017-02
42017-03

 

Now I need to get the same result using DAX. Can someone please help me to get the same using DAX?

4 Replies

  • popov's avatar
    popov
    Resolver III
    8 years ago

    Hello, Anonymous

    Try this query

     

    EVALUATE
    FILTER (
        ADDCOLUMNS (
            VALUES ( Transactions[Year-Month] ),
            "RowCount", CALCULATE (
                DISTINCTCOUNT ( Transactions[ID] ),
                Transactions[Billing Type] = "Credit Card"
            )
        ),
        [RowCount] >= 3
    )

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi popov, 

      Thanks for your reply!

      I'm getting the following error

      Argument '3' in CALCULATE function is required.