DAX Measure

[Anonymous]

Hi All,

Need a help in resolving my query.

I have created a calculated column Name  (Remit Status) which is working fine , when i am placing in table(Logic below)

remit status =

if(ProjectPhases[actual_date_01]=BLANK()

&& NOW()>ProjectPhases[Remit_Target_Date],"Overdue",

if(ProjectPhases[actual_date_01]=BLANK()

&& NOW()>DATEADD(ProjectPhases[Remit_Target_Date],-28,DAY),"Due in 4 weeks",

if(ProjectPhases[actual_date_01]=TODAY()

    || (ProjectPhases[actual_date_01]<(ProjectPhases[Remit_Target_Date])) ,"Approved on Time",

if(ProjectPhases[actual_date_01]=TODAY()

    || (ProjectPhases[actual_date_01]>(ProjectPhases[Remit_Target_Date])) ,"Approved Late", " "

))))

Now, my requirement is to place each status(eg: Overdue, due in 4 weeks , Approved on Time & Approved Late) as seperate DAX measure which needs to be mentioned as count in Card Visual.

DAX1:  (Just calculating, based on the remit status column which i created above)

remit_overdue =
VAR cnt =
COUNTX (
FILTER (
ProjectPhases,
ProjectPhases[remit status]
= "Overdue"
),
MIN ( Projects[AuditCode] )
)
VAR ret_cnt =
IF ( cnt = BLANK (), 0, cnt )
RETURN
ret_cnt

Dax2 :

Remits_Duein4weeks =

VAR cnt =

COUNTX (

FILTER (

ProjectPhases,

            ProjectPhases[actual_date_01]=BLANK()

            &&

ProjectPhases[actual_date_01]

> DATE ( YEAR ( NOW () ), MONTH ( NOW () ), DAY ( NOW () ) )

&& ProjectPhases[Remit_Target_Date]

> DATE ( YEAR ( NOW () ), MONTH ( NOW () ), DAY ( NOW () ) - 28 )

),

MIN ( Projects[AuditCode] )

)

VAR ret_cnt =

IF ( cnt = BLANK (), 0, cnt )

RETURN

ret_cnt

Please note both the dax where not working.

Need your Assistance.

Cheers,

Saiiii

[Anonymous · Accepted solution]

Hi @amitchandak 

Please correct me if I wrongly understood your problem.

You want to calculate the number of rows for ProjectPhases[Remit Status]="Overdue" and ProjectPhases[Remit Status]="Due in 4 weeks", right ?

I create a sample with the calculated column you provided .You can see the screenshot below .

From the data ,we can see the numbers for ProjectPhases[Remit Status]="Overdue" and ProjectPhases[Remit Status]="Due in 4 weeks" respectively are 2 and 1.Because I don’t know the field Projects[AuditCode] ,so I create the formulas are different from yours .

Then we create measures to count .

remit_overdue =
var cnt=CALCULATE(COUNTROWS(ProjectPhases),FILTER(ProjectPhases,ProjectPhases[Remit Status]="Overdue"))
return IF(cnt=BLANK(),0,cnt)

Remits_Due in 4weeks =
VAR cnt =CALCULATE(COUNT(ProjectPhases[Remit Status]),FILTER (ProjectPhases,ProjectPhases[actual_date_01]=BLANK() && NOW()>DATEADD(ProjectPhases[Remit_Target_Date],-28,DAY) && NOW()<ProjectPhases[Remit_Target_Date]))
return IF( cnt = BLANK(),0,cnt)

The result is as shown:

I have attached my pbix file ,you can refer to it .

Best Regards

Community Support Team _ Ailsa Tao

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

[amitchandak]

@Anonymous , not sure for the reason to take min. Try measure like this one

COUNTX (
FILTER (
ProjectPhases,
ProjectPhases[remit status]
= "Overdue"
),
Projects[AuditCode]
) +0