Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

DAX help needed

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(distinc...
Need Help
v-deddai1-msft's avatar
v-deddai1-msft
Icon for Community Support rankCommunity Support
5 years ago

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