Forum Discussion

rpiboy_1's avatar
rpiboy_1
Icon for Helper V rankHelper V
2 years ago

Fact Value Unit Conversion accounting for different Summarization

I have a Semantic Model that is intended to be consummed by multiple stake-holders to build their own Reports (true self-service BI). To that end I've been trying to make it as user friendly as possible, names that articulate purpose, descriptions, etc. There are several values in my Fact tables where I've picked a default summarization, Average for instnace, but depending upon the end user, Sum or Max or Min might make just as much sense.

 

However, some of these values are in specific units (area to be exact) and therefore if someone wants to see Square Feet versus Square Meters the value(s) need to be converted. I've written some DAX to handle the conversion, however it occured to me, that by doing so the summarization is now implicit by way of the my DAX (SUMX vs AVGX for example).

 

Is there an elegant way in DAX to still give the end user flexibility, in changing the Summarization without having to write multiple new measures? Could I use calcualtion groups?

For reference here is the DAX, pretty straight-forward:

Designed Area (converted) = 
VAR UnitCheck = HASONEVALUE(Units[Name])

VAR UnitFactor = IF(UnitCheck = TRUE(), SUM(Units[Area (SqM/SqFt)]), 1)

VAR ConvertVal = IF(UnitCheck = TRUE(), SUMX('Latest Project Data', UnitFactor * 'Latest Project Data'[Sum Designed Area]), "Invalid Conversion Value")

RETURN
ConvertVal

1 Reply