Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Producing SQL partition over in DAX

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.

 

Need Help

4 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

    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]) ))

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      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

       

    • jmweekes's avatar
      jmweekes
      Advocate II
      4 years ago

      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:

      CustomerIdLastNameStateLocalTypeStateCnt
      1SMITHCARural2
      2JONESCAUrban2
      6FOSTERKSRural3
      7KINGKSRural3
      5LONGKSUrban3
      3HAWKINSMORural2
      4OWENMOUrban2


      Slicer where LocalType = Rural:

      CustomerIdLastNameStateLocalTypeStateCnt
      1SMITHCARural1
      6FOSTERKSRural2
      7KINGKSRural2
      3HAWKINSMORural1

       

      Slicer where LocalType = Urban:

      CustomerIdLastNameStateLocalTypeStateCnt
      2JONESCAUrban1
      5LONGKSUrban1
      4OWENMOUrban1
       
       
      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!
  • v-easonf-msft's avatar
    v-easonf-msft
    Community Support
    5 years ago

    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