DAX help needed

[Anonymous]

Hi Power Users,

I'm still learning powerbi. I need help in getting below sql query write in power bi using dax. 

select sum(Monthcount), sum(Monthcount)/12 from (select indexid, count(distinct MonthID) as MonthCount, Count(Distinct indexId) as Indexid_count, count(distinct MonthID)/6 as ABC from Table-1
Innerjoin Table-2 on Table-1. MonthID= Table-2.MonthID

Where monthyear between '202001' and '202010'
group by indexid

I need this query to write using DAX. 

Thanks

[v-deddai1-msft]

Hi @Anonymous ,

Would you please try something like:

sum(Monthcount)/12 =
VAR a =
    SUMMARIZE (
        FILTER (
            NATURALINNERJOIN ( Table - 2, Table - 1 ),
            'Table-2'[monthyear] >= 202001
                && 'Table-2'[monthyear] <= 202010
        ),
        'Table-2'[indexid],
        "MonthCount", DISTINCTCOUNT ( 'Table-2'[MonthID] ),
        "ABC", DISTINCTCOUNT ( 'Table-2'[MonthID] ) / 6
    )
RETURN
    SUMX ( a, [MonthCount] ) / 12

sum(Monthcount) =  VAR a =
    SUMMARIZE (
        FILTER (
            NATURALINNERJOIN ( Table - 2, Table - 1 ),
            'Table-2'[monthyear] >= 202001
                && 'Table-2'[monthyear] <= 202010
        ),
        'Table-2'[indexid],
        "MonthCount", DISTINCTCOUNT ( 'Table-2'[MonthID] ),
        "ABC", DISTINCTCOUNT ( 'Table-2'[MonthID] ) / 6
    )
RETURN
    SUMX ( a, [MonthCount] ) 

If you need accurate dax formula, please show us some sample data by onedrive for business.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[amitchandak]

@Anonymous , You can have measures like this

sumx(values('Table-1'[indexid]), distinctcount('Table-1'[indexid]))
sumx(values('Table-1'[indexid]), distinctcount('Table-1'[MonthID]))
sumx(values('Table-1'[indexid]), distinctcount('Table-1'[MonthID]))/12

Both Tables should join on MonthID in power BI and where you can take filter in the slicer

You can refer my Dax vs SQL series, if that can help https://www.youtube.com/watch?v=WlvQ_SGy4iA&list=PLPaNVDMhUXGZNyKU0PgG2g3P0c6CPjMnj