Requirement of converting the SQL query to DAX

[Anonymous]

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

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

4	2017-01
5	2017-02
4	2017-03

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

[Zubair_Muhammad]

HI @Anonymous

Try this

Go to Modelling Tab>> NEW TABLE

Table =
FILTER (
    SUMMARIZE (
        Transactions,
        Transactions[Year-Month],
        "Row Count", CALCULATE (
            DISTINCTCOUNT ( Transactions[ID] ),
            Transactions[Billing Type] = "Credit Card"
        )
    ),
    [Row Count] >= 3
)

[Anonymous]

Hi @Zubair_Muhammad,

Thanks for your help!

I need to use this as New Measure of Power BI.

When I use your script, I'm getting the following error.

The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value.

[Zubair_Muhammad · Accepted solution]

@Anonymous

I gave you a calculated table earlier.

Here is the MEASURE

Row_Count =
VAR mycount =
    CALCULATE (
        DISTINCTCOUNT ( Transactions[ID] ),
        Transactions[Billing Type] = "Credit Card"
    )
RETURN
    IF ( Mycount >= 3, mycount )

[Anonymous]

Hi @Zubair_Muhammad

Can you also help me with some other requirement?