topic Need DAX Measure Correction in DAX Commands and Tips

Need DAX Measure Correction

Khalidshurafa — Mon, 27 May 2024 12:47:28 GMT

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(

TotalBUAForAsset

)

SubAssets[Asset Name] = EntireAssetProgress

)

Re: Need DAX Measure Correction

Anonymous — Tue, 28 May 2024 01:51:03 GMT

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.

Re: Need DAX Measure Correction

Khalidshurafa — Tue, 28 May 2024 05:01:55 GMT

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(

TotalBUAForAsset

)

SubAssets[Asset Name] = EntireAssetProgress

)

Thanks mate,