Forum Discussion

roopa_123's avatar
roopa_123
Helper I
4 years ago

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. 


I am expecting the visualization to look something like below:

 

 

Kindly suggest how can I implement this using DAX?

Need Help
Show and Tell

7 Replies

  • webportal's avatar
    webportal
    Impactful Individual
    4 years ago

    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's avatar
      roopa_123
      Helper I
      4 years ago

      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!

       

  • webportal's avatar
    webportal
    Impactful Individual
    4 years ago

    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's avatar
      roopa_123
      Helper I
      4 years ago

      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's avatar
        webportal
        Impactful Individual
        4 years ago

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

  • roopa_123's avatar
    roopa_123
    Helper I
    4 years ago

    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!