Producing SQL partition over in DAX

[Anonymous]

Hi All,

I have a requirement to add several columns that aggerate scores from the table while respecting slicers on the page.

For adding these columns SQL is explained as below

SELECT  *,

          sum(scores) over(partition by newsroom, GROUP_ID, GROUP_VALUE) as sum_of_scores,

          sum(scores) over(partition by newsroom, GROUP_ID) as sum_of_scores_grouped_by_group_id,

          sum(scores) over(partition by newsroom, GROUP_VALUE) as sum_of_scores_grouped_by_group_value,

          sum(scores) over(partition by newsroom) as sum_of_scores_grouped_by_newsroom,

          count(distinct single_byline) over(partition by newsroom, GROUP_ID, GROUP_VALUE) as count_of_byline,

          count(distinct single_byline) over(partition by newsroom, GROUP_ID) as count_of_byline_grouped_by_group_id,

          count(distinct single_byline) over(partition by newsroom, GROUP_VALUE) as count_of_byline_grouped_by_group_value,

          count(distinct single_byline) over(partition by newsroom) as count_of_byline_grouped_by_newsroom

  FROM    Table

  WHERE   pubdate >= '2021-01-01' AND pubdate <= '2021-03-01'

Can we achieve this in Power BI please let me know.

Any help would be highly appreciated.

Thanks in Advance,

Neelofar Shama.

[v-easonf-msft]

Hi,  @Anonymous 

Could you please tell me whether your problem has been solved?

If yes, please accept the helpful answer as solution to close this thread. 
For now, there is no content of description in the thread. f you still need help, please share more details.

Best Regards,
Community Support Team _ Eason

[amitchandak]

@Anonymous , I power bi display * means you need put all column in visual. That too you need to have index/ ID column that will make sure data is grouped.

Allexcept can help in measure. The date can be filter from the slicer

calculate(sum(Table[scores]),allexcept(Table, [GROUP_ID], [GROUP_VALUE]))

or

calculate(sum(Table[scores]),filter(allselected(Table), [GROUP_ID] = max([GROUP_ID]) && [GROUP_VALUE] =max([GROUP_VALUE]) ))

[jmweekes]

Thank you for this answer!  For over 12 hours I have been trying to figure out how to do a simple "COUNT OVER PARTITION" in DAX that honors slicer selections.  Your post helped me to finally put the pieces together.

My example project was a simple table of customers with state and "Local Type" (Rural or Urban). 

Table Name = CustomerLocal.

I put the data in a table and needed to count the number of customers per state (StateCnt) and have StateCnt adjust based on a slicer on LocalType. 

No Slicer:

CustomerId	LastName	State	LocalType	StateCnt
1	SMITH	CA	Rural	2
2	JONES	CA	Urban	2
6	FOSTER	KS	Rural	3
7	KING	KS	Rural	3
5	LONG	KS	Urban	3
3	HAWKINS	MO	Rural	2
4	OWEN	MO	Urban	2

Slicer where LocalType = Rural:

CustomerId	LastName	State	LocalType	StateCnt
1	SMITH	CA	Rural	1
6	FOSTER	KS	Rural	2
7	KING	KS	Rural	2
3	HAWKINS	MO	Rural	1

Slicer where LocalType = Urban:

CustomerId	LastName	State	LocalType	StateCnt
2	JONES	CA	Urban	1
5	LONG	KS	Urban	1
4	OWEN	MO	Urban	1

With the help of this post I was finally able to figure out the correct DAX to cacluate StateCnt:

StateCnt =
     /* ALLSELECTED() removes ALL filters (namely row context) EXCEPT slicers,
      * [State] = SELECTEDVALUE([State]) filters to the State for current row to get 
      * the desired Partition for COUNT() */
    CALCULATE(
        COUNT(CustomerLocal[CustomerId]),
        FILTER(
            ALLSELECTED(CustomerLocal),
            CustomerLocal[State] = SELECTEDVALUE(CustomerLocal[State])
        )
    )

I know what it does and sorta know why it works, but would welcome any clear explanation someone can provide, particularly the interaction of ALLSELECTED within FILTER().

I probably need to provide a default value for SELECTEDVALUE(), but don't know under what data circumstances it would be required.

Thank you, amitchandak!

[Anonymous]

Thank you for the reply, I tried your solution but the results so not match with my SQL output in Power BI.

select

sum(scores) over(partition by newsroom, GROUP_ID) as sum_of_scores_grouped_by_group_id,
sum(scores) over(partition by newsroom, GROUP_VALUE) as sum_of_scores_grouped_by_group_value,

sum(scores) over(partition by newsroom) as sum_of_scores_grouped_by_newsroom,
count(distinct single_byline) over(partition by newsroom, GROUP_ID, GROUP_VALUE) as count _of_byline,
count(distinct single_byline) over(partition by newsroom, GROUP_ID) as count_of_byline_grouped_by_group_id,
count(distinct single_byline) over(partition by newsroom, GROUP_VALUE) as count_of_byline_grouped_by_group_value,
count(distinct single_byline) over(partition by newsroom) as count_of_byline_grouped_by_newsroom
FROM Table
WHERE pubdate >= '2021-01-01' AND pubdate <= '2021-03-01'

this is my SQL query which gives results as shown below

In Power BI: I am getting 546491604 whereas in sql it is 6392482