Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

DAX Measure

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
 
Need Help
  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

2 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    5 years ago

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

     

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    5 years ago

    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.