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 ...
'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,
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,