Forum Discussion

Khalidshurafa's avatar
Khalidshurafa
Frequent Visitor
2 years ago
Solved

Need DAX Measure Correction

Dears,
I need help with correcting the below DAX measure in which I target measuing progress from latest available date in the data report as it includes data from several dates


please note that it only works well for the second argument but there is still an issue with the first and third ones,

 

Overall Actual Progress =
 VAR LatestDate = CALCULATE(MAX('Project Calendar'[Date]), ALL('Weekly Progress'[Reporitng Date]))
 VAR SingleBuildingProgress = SELECTEDVALUE(SubAssets[BuildingName])
 VAR EntireAssetProgress = SELECTEDVALUE(SubAssets[Asset Name])
 VAR SelectedAssetBUA = SELECTEDVALUE('Weekly Progress'[Building BUA])
 VAR TotalBUAForAsset = CALCULATE(SUM('Weekly Progress'[Building BUA]), SubAssets[Asset Name] = EntireAssetProgress, 'Weekly Progress'[Reporitng Date] = LatestDate)
 
 RETURN
 IF(
    ISBLANK(EntireAssetProgress),
    SUMX(
        FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),
        'Weekly Progress'[Actual Progress] * 'Weekly Progress'[Building BUA] / SUMX(
            FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),
            'Weekly Progress'[Building BUA]
        )
    ),
    IF(
        NOT(ISBLANK(SingleBuildingProgress)),
        LOOKUPVALUE(
            'Weekly Progress'[Actual Progress],
            'Weekly Progress'[BuildingName], SingleBuildingProgress, 'Weekly Progress'[Reporitng Date], LatestDate
        ),
        CALCULATE(
            SUMX(
                FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),
                'Weekly Progress'[Building BUA] * 'Weekly Progress'[Actual Progress] * DIVIDE(
                    1,
                    TotalBUAForAsset
                )
            ),
            SubAssets[Asset Name] = EntireAssetProgress
        )
    )
)
'Need help'
'Need help' 'DAX'
  • Khalidshurafa's avatar
    Khalidshurafa
    2 years ago

    Hi Anonymous 
    I actually was able to fix it myself after deep analysis of the measure,

     

    I actually had one reference inside the varibel called LatestDate to another table in the data model which wasnt supposed to be there,

     

    Below is the correct one,

     

    Overall Actual Progress =
     VAR LatestDate = CALCULATE(MAX('Weekly Progress'[Reporitng Date]), ALL('Weekly Progress'[Reporitng Date]))
     VAR SingleBuildingProgress = SELECTEDVALUE(SubAssets[BuildingName])
     VAR EntireAssetProgress = SELECTEDVALUE(SubAssets[Asset Name])
     VAR SelectedAssetBUA = SELECTEDVALUE('Weekly Progress'[Building BUA])
     VAR TotalBUAForAsset = CALCULATE(SUM('Weekly Progress'[Building BUA]), SubAssets[Asset Name] = EntireAssetProgress, 'Weekly Progress'[Reporitng Date] = LatestDate)
     
     RETURN
     IF(
        ISBLANK(EntireAssetProgress),
        SUMX(
            FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),
            'Weekly Progress'[Actual Progress] * 'Weekly Progress'[Building BUA] / SUMX(
                FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),
                'Weekly Progress'[Building BUA]
            )
        ),
        IF(
            NOT(ISBLANK(SingleBuildingProgress)),
            LOOKUPVALUE(
                'Weekly Progress'[Actual Progress],
                'Weekly Progress'[BuildingName], SingleBuildingProgress, 'Weekly Progress'[Reporitng Date], LatestDate
            ),
            CALCULATE(
                SUMX(
                    FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),
                    'Weekly Progress'[Building BUA] * 'Weekly Progress'[Actual Progress] * DIVIDE(
                        1,
                        TotalBUAForAsset
                    )
                ),
                SubAssets[Asset Name] = EntireAssetProgress
            )
        )
    )
     
    Thanks mate,

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Hi Khalidshurafa 

     

    Please share more details:

    What is the relationship between your tables?

    Which visual you're using?

    What is the second argument, if possible, please share some screenshot to explain what issue you've met and your target outcome.

    Please share some raw data(Exclude sensitive data) or create some sample data, so that we can help you better.

    You can refer the following links to share the required info:

    How to provide sample data in the Power BI Forum

    How to Get Your Question Answered Quickly

     

    Best Regards

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

    • Khalidshurafa's avatar
      Khalidshurafa
      Frequent Visitor
      2 years ago

      Hi Anonymous 
      I actually was able to fix it myself after deep analysis of the measure,

       

      I actually had one reference inside the varibel called LatestDate to another table in the data model which wasnt supposed to be there,

       

      Below is the correct one,

       

      Overall Actual Progress =
       VAR LatestDate = CALCULATE(MAX('Weekly Progress'[Reporitng Date]), ALL('Weekly Progress'[Reporitng Date]))
       VAR SingleBuildingProgress = SELECTEDVALUE(SubAssets[BuildingName])
       VAR EntireAssetProgress = SELECTEDVALUE(SubAssets[Asset Name])
       VAR SelectedAssetBUA = SELECTEDVALUE('Weekly Progress'[Building BUA])
       VAR TotalBUAForAsset = CALCULATE(SUM('Weekly Progress'[Building BUA]), SubAssets[Asset Name] = EntireAssetProgress, 'Weekly Progress'[Reporitng Date] = LatestDate)
       
       RETURN
       IF(
          ISBLANK(EntireAssetProgress),
          SUMX(
              FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),
              'Weekly Progress'[Actual Progress] * 'Weekly Progress'[Building BUA] / SUMX(
                  FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),
                  'Weekly Progress'[Building BUA]
              )
          ),
          IF(
              NOT(ISBLANK(SingleBuildingProgress)),
              LOOKUPVALUE(
                  'Weekly Progress'[Actual Progress],
                  'Weekly Progress'[BuildingName], SingleBuildingProgress, 'Weekly Progress'[Reporitng Date], LatestDate
              ),
              CALCULATE(
                  SUMX(
                      FILTER('Weekly Progress', 'Weekly Progress'[Reporitng Date] = LatestDate),
                      'Weekly Progress'[Building BUA] * 'Weekly Progress'[Actual Progress] * DIVIDE(
                          1,
                          TotalBUAForAsset
                      )
                  ),
                  SubAssets[Asset Name] = EntireAssetProgress
              )
          )
      )
       
      Thanks mate,