October 23, 2017 at 6:51 am
I have a cube I am weorking on building using tabular mode. I have set the relationship in the .bim between two tables using the COAKEY and Accountkey fields. However, when attempting to use SUMX(FILTER(RELATED) nothing shows up in the prompt when I attempt to use related, and when I enter the table and column the expression errors out. Is there something I am missing?
October 23, 2017 at 6:59 am
The model looks straight forward. I'm not sure what you're doing as to why you'd need to use related. Are you creating a measure or writing a DAX query? Posting the whole expression and what you're trying to accomplish would help. Do you have a CALCULATE if necessary?
October 23, 2017 at 7:13 am
I am attempting to create a measure using the related to determine if GLL_Amount is categorized as REVENUE or COGS. In the case of revenue the formula I am trying to use is
Revenue:=SUMX(FILTER(Fact_Transaction,RELATED(DimCoa[COA_Type]) = "Revenue", ),Fact_Transaction[GLL_Amount])
October 23, 2017 at 7:53 am
And I figured it out. I had screwed up on the COAKEY field so that there were no matches when attempting to use RELATED. However, I am not 100% sure why, but it still shows up underlined in red when entering in the formula, I am guessing an intellisense issue, but I cannot Find Where to actually update intellisense in the editor.
October 23, 2017 at 12:36 pm
This doesn't seem like something that requires a SUMX. Wouldn't the following give the same result? SUMX may give worse performance because it can use the formula engine instead of the storage engine.
Revenue :=
CALCULATE (
SUM ( Fact_Transaction[GLL_Amount] ),
FILTER ( VALUES ( DimCoa[COA_Type] ), DimCoa[COA_Type] = "Revenue" )
)
October 23, 2017 at 12:46 pm
Brian Carlson - Monday, October 23, 2017 12:36 PMThis doesn't seem like something that requires a SUMX. Wouldn't the following give the same result? SUMX may give worse performance because it can use the formula engine instead of the storage engine.
Revenue :=
CALCULATE (
SUM ( Fact_Transaction[GLL_Amount] ),
FILTER ( VALUES ( DimCoa[COA_Type] ), DimCoa[COA_Type] = "Revenue" )
)
The calculate method does work and accomplishes the same thing, I am very new to DAX and Tabular models, so It is good to know that there was a better way to accomplish this. Do you have any idea why all my measures show up blank in PowerBI Desktop when I attempt to use this model?
See https://www.sqlservercentral.com/Forums/1903964/Measures-showing-up-Blank-in-Power-BI
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply