Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
roopa_123
Helper I
Helper I

DAX query

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. 

roopa_123_0-1649077839913.png
I am expecting the visualization to look something like below:

 

roopa_123_1-1649078052778.png

 

Kindly suggest how can I implement this using DAX?

7 REPLIES 7
roopa_123
Helper I
Helper I

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

webportal
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.

webportal
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

Announcements
September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.