Forum Discussion

jbeans's avatar
jbeans
Frequent Visitor
2 years ago
Solved

Matrix not summing / measure not working as expected

Hi All    I have the below DAX that is working as expected (it appears). It is checking the earliest finished date and if it meets certain criteria assigns a value of 1. If not 0.    I have this ...
General Comment
Need Help
  • jbeans's avatar
    jbeans
    2 years ago

    Anonymous lbendlin 

    The below worked for me to retrieve the earliest date and if column is blank assign 1 else 0, leaving the code here in case anyone else comes across this post 

     

    Thanks for looking in to this issue for me 

     

    Measure RFT LOC = 

SUMX(
    'lnd AWComponentLibraryApproval',
    IF(
        'lnd AWComponentLibraryApproval'[Reason_for_rejection] = BLANK() &&
        'lnd AWComponentLibraryApproval'[Approver_Role] = "LOC REVIEWER / APPROVER" &&
        'lnd AWComponentLibraryApproval'[Approval_finish_date] = CALCULATE(MIN('lnd AWComponentLibraryApproval'[Approval_finish_date]), 
            ALLEXCEPT('lnd AWComponentLibraryApproval', 'lnd AWComponentLibraryApproval'[AW_Order_ID])),    
        1,
        0
    )
)
Anonymous's avatar
Anonymous
Not applicable
2 years ago

Hi jbeans ,

You can create another new measure as below base on the measure [Measure RFT LOC] to get the correct total value and replace the measure [Measure RFT LOC]  with the new measure on the table visual:

Measure =
SUMX (
    GROUPBY (
        'lnd AWComponentLibraryApproval',
        'lnd AWComponentLibraryApproval'[AW_Order_ID],
        'lnd AWComponentLibraryApproval'[Approval_finish_date],
        'lnd AWComponentLibraryApproval'[Approver_Role],
        'lnd AWComponentLibraryApproval'[Reason_for_rejection]
    ),
    [Measure RFT LOC]
)

In addition, you can refer the following links to try to solve your problem...

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Dax for Power BI: Fixing Incorrect Measure Totals

 

If the above one can't help you get the expected result, please provide some raw data in your table (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It would be helpful to find out the solution. You can refer the following link to share the required info:

How to provide sample data in the Power BI Forum

 

And It is better if you can share a simplified pbix file. You can refer the following link to upload the file to the community. Thank you.

How to upload PBI in Community

Best Regards

jbeans's avatar
jbeans
Frequent Visitor
2 years ago

Anonymous lbendlin 

The below worked for me to retrieve the earliest date and if column is blank assign 1 else 0, leaving the code here in case anyone else comes across this post 

 

Thanks for looking in to this issue for me 

 

Measure RFT LOC = 

SUMX(
    'lnd AWComponentLibraryApproval',
    IF(
        'lnd AWComponentLibraryApproval'[Reason_for_rejection] = BLANK() &&
        'lnd AWComponentLibraryApproval'[Approver_Role] = "LOC REVIEWER / APPROVER" &&
        'lnd AWComponentLibraryApproval'[Approval_finish_date] = CALCULATE(MIN('lnd AWComponentLibraryApproval'[Approval_finish_date]), 
            ALLEXCEPT('lnd AWComponentLibraryApproval', 'lnd AWComponentLibraryApproval'[AW_Order_ID])),    
        1,
        0
    )
)