DAX query

[roopa_123]

Hi,
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?

[roopa_123]

Hi,

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!

[webportal]

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.

[roopa_123]

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?

[webportal]

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

[roopa_123]

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

[webportal]

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.

[roopa_123]

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!