CODEWITHSUNDEEP
sqlselectgroup-bypowerbidax
Scrub
Scrub

Reputation: 84

SQL select & group-by as DAX

I am trying to create a new measure in power BI from my database - i have a table from which i can run a query and get a csv file of the result but want to set the report up to look at up to date data and the csv is only a snapshot. I count the individual row entries (error occurrences) to create a count of the errors and wish to group these by date (RTO_UPDATED) and location (RTO_BLOCK) for analysis on when and where we are experiencing these errors

This is the code below:

select count(*) as Errors,
        cast(RTO_UPDATED as date) as Date,
        RTO_BLOCK
 FROM [MIS_Apps].[dbo].[LiveRTOLocationErrorsHistorical]
  group by cast(RTO_UPDATED as date),
        RTO_BLOCK

If someone could help me convert this to DAX it would be greatly appreciated

I have tried this (below) but apparently the evaluate syntax is incorrect and i am unsure how to change it when a group-by is involved?

  EVALUATE SELECTCOLUMNS( 
    count(*) as Errors, cast(RTO_UPDATED as date) as Date, RTO_BLOCK 
    FROM [MIS_Apps].[dbo].[LiveRTOLocationErrorsHistorical] 
    group by cast(RTO_UPDATED as date), RTO_BLOCK 
    )

Upvotes: 1

Views: 98

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

Try this following with adding a custom table in your Power BI report. Add this below code in the Custom Table generation window.

Group_by_result = 
GROUPBY (
    // -- This is the table name
    LiveRTOLocationErrorsHistorical,
    // -- Below two column are GROUP BY columns
    LiveRTOLocationErrorsHistorical[RTO_UPDATED],
    LiveRTOLocationErrorsHistorical[RTO_BLOCK ],
    // -- Here comes the aggregation
    "Total_count", COUNTX(CURRENTGROUP(), 1)
)

Upvotes: 1

Related Questions