Showing results for 
Search instead for 
Did you mean: 

Fabric is Generally Available. Browse Fabric Presentations. Work towards your Fabric certification with the Cloud Skills Challenge.

Helper I
Helper I

DAX query

I have my report visualization as below. The Instance column has various sources in my data. So, for each instance, the table visual show should only 1 row per Instance i.e., if the Instance is 'ABC', then I need to have a column for that instance only once and then columns like Latest successful runs, total succesful runs, latest failed runs, total failed runs. 

I am expecting the visualization to look something like below:




Kindly suggest how can I implement this using DAX?

Helper I
Helper I


Here in the above expected result screenshot, all the four fields i.e., LatestSuccesfulRuns, TotalSuccessfulRuns, LatestFailedRuns, TotalFailedRuns are as follows :
LatestSuccesfulRuns : Latest(startdate)
TotalSuccessfulRuns: Count(startdate)
LatestFailedRuns :Latest(startdate)
TotalFailedRuns: count(startdate)
Only thing is that, for each sourceinstance, one only row should be present for that sourceinstance and all the above four fields should appear.
How to achieve this in power query editor? Can we do it using group by transformation? If so, kindly help me with the steps.
Help is appreciated!

Impactful Individual
Impactful Individual

Hi @roopa_123 


Yes, you may also build a table with that aggregation. Select "Modelling" > "New table" from the top menu and write the following expression:


New table = SUMMARIZE('Table', 'Instance',  "Last Run", MAX('Table'[Latest successful runs]), "Total Runs", SUM('Table'[Runs]))


This should create a new table in your data model.

Hi @webportal ,


Tried your solution, but it is not working as per the expected sample that I need 🙁
I also need to use other columns from my original table to be used in the reports. In that case, how can I write a DAX to get dynamic table?

Is there any other way?

Impactful Individual
Impactful Individual

Ok, can you share the pbix or a sample dataset?

@webportal : Apologies, I cannot share it as it contains client sensitive data.

Impactful Individual
Impactful Individual

Try the following DAX measures:


Latest successful runs = MAX('Table'[Latest successful runs])

Totals = SUM('Table'[Runs])


'Table' refers to the table where you have the data.


Hi @webportal ,

Thanks for your quick reply.

I have actually used Latest(startdate) and Count(startdate) for LatestSuccessfulRuns and TotalSuccessfulRuns columns respectively (here startdate is one of the columns of my table). But how do I get the values for failed runs and failed count?
Is there any other way to get my requirement to be implemented? something like creating a dynamic tables using DAX?

Help is appreciated! Thanks in advance!


Helpful resources

PBI November 2023 Update Carousel

Power BI Monthly Update - November 2023

Check out the November 2023 Power BI update to learn about new features.

Power BI Fabric Summit Carousel

The largest Power BI and Fabric virtual conference

130+ sessions, 130+ speakers, Product managers, MVPs, and experts. All about Power BI and Fabric. Attend online or watch the recordings.

Top Solution Authors
Top Kudoed Authors