DAX-How to use GROUPBY and IF in DAX

[roopa_123]

Hi PowerBi Community,

I need a help on my query.

I have created a new table based on main table(PipelineRuns) using the below DAX query.

FailedRuns= FILTER(FILTER(
GROUPBY(
PipelineRuns,
PipelineRuns[SourceInstance],
PipelineRuns[PipelineName],
PipelineRuns[status],
"LatestFailedRuns",
MAXX(CURRENTGROUP(),PipelineRuns[start]),
"TotalFailedRuns",
COUNTX(CURRENTGROUP(),PipelineRuns[start])),
PipelineRuns[status]="Failed"),
PipelineRuns[PipelineName]="mypipelinename")

I've used a table visual as below.

As per my requirement, I need to get the report visual in the below format i.e., the 'TotalFailedRuns' should be filled with '0' if there is no data for 'LatestFailedRuns'

How can this be achieved using DAX? / Can we use GROUPBY and IF condition together to achieve the above requirement? If there is any other way, kindly suggest.
Help is appreciated! Thanks in advance!

[Anonymous · Accepted solution]

Hi @roopa_123 ,

According to this: "TotalFailedRuns" column should show zeros(0) instead of blank 

Simply, you could add "+0" to the end of the [TotalFailedRuns] measure, for example:

Or use IF() to replace blank value:

TotalFailedRuns = 
var _total=SUM('Table (2)'[Value])
return IF(_total=BLANK(),0,_total)

Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Greg_Deckler]

@roopa_123 I *think* you want:

FailedRuns= 
FILTER(
  GROUPBY(
    FILTER(PipelineRuns,PipelineRuns[PipelineName]="mypipelinename"),
    PipelineRuns[SourceInstance],
    PipelineRuns[PipelineName],
    PipelineRuns[status],
    "LatestFailedRuns",MAXX(CURRENTGROUP(),PipelineRuns[start]),
    "TotalFailedRuns",COUNTX(CURRENTGROUP(),PipelineRuns[start])
  ),
  PipelineRuns[status]="Failed"
)

Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882

Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.

[roopa_123]

Hi @Greg_Deckler ,

Please find the below sample data and expectedoutput.
SampleData:

PipelineName	start	status	sourceInstance
PL_AB	28-02-2022 10:18:22	Succeeded	ABC
PL_CD	28-02-2022 10:18:41	Succeeded	DEF
PL_EF	28-02-2022 10:18:54	Succeeded	XYZ
PL_GH	28-02-2022 10:19:31	Failed	ABC
PL_AB	28-02-2022 10:19:07	Succeeded	ABC
PL_AB	1/3/22 10:10	Failed	ABC
PL_AB	1/3/22 10:14	Succeeded	ABC
PL_AB	1/3/22 10:23	Failed	ABC
PL_EF	1/3/22 10:10	Failed	DEF
PL_EF	1/3/22 10:10	Failed	DEF
PL_EF	1/3/22 10:10	Failed	DEF
PL_GH	19/3/2022  10:20	Succeeded	ABC
PL_GH	19/3/2022  6:07	Succeeded	DEF
PL_GH	19/3/2022  6:10	Succeeded	XYZ
PL_GH	1/12/22 10:10	Failed	XYZ
PL_GH	1/13/22 10:10	Failed	XYZ

ExpectedOutput:

SourceInstance	LatesSuccessfulRuns	Total SuccessfulRuns	LatestFailureRuns	TotalFailedRuns
ABC	19-3-2022 10:20	703	1/3/22 10:10	3
DEF	19-3-2022 6:07	3		0
XYZ	19-3-2022 6:10	3		0

[Greg_Deckler]

@roopa_123 Maybe something like this although I am still not certain of the logic of how to calculate the Total Failed Runs.

Measure = 
    VAR __Table =
        ADDCOLUMNS(
            FILTER(
                ADDCOLUMNS(
                    GROUPBY('Table2',[PipelineName]),
                    "LastFailed",MAXX(FILTER('Table2',[PipelineName] = EARLIER([PipelineName]) && [status] = "Failed"),[start]),
                    "LastSucceed",MAXX(FILTER('Table2',[PipelineName] = EARLIER([PipelineName]) && [status] = "Succeeded"),[start])
                ),
                [LastFailed] > [LastSucceed]
            ),
            "Count",COUNTROWS(FILTER('Table2',[PipelineName] = EARLIER([PipelineName]) && [status] = "Failed"))
        )
RETURN
    SUMX(__Table,[Count])

[roopa_123]

Hi @Greg_Deckler ,

Thanks for your reply.

I've actually developed the model and built report and it is working as expected but only the thing needed to show in my report is that "TotalFailedRuns" column should show zeros(0) instead of blank on my table visuals.

Expected report should look something like below:

How can this be achieved using DAX? Thanks in advance!