Hi All,
I’m trying to apply table expansion to the model below.
I’ve dimension tables Client, Account and Contract I’m using to slice cost figures in the Financials fact table.
I was able to slice by Client and Contract, but I’m asked to slice by Account as well.
Selecting a Client I need to show by Account and Contract the sum of Cost in the Financial table
I’m trying to apply table expansion using as a filter table the fact table, but it’s not working.
What is the right approach in such a case?
Unfortunately, I cannot make any change to the model, hosted in SAAS db.
Thanks for help
Roberto
@Roberto,
If you can’t change the relationship, you need to do this using DAX. What I suggest is use Account field as a filter and get selected value using selectedvalue function. You can use this selected value to filter financials table by writing DAX command with filter() function. Since client and contract also connected to financials, selections coming from those tables will automatically filter the financial table using context.
Thanks @akila789 for your suggestion, but I cannot add any other filter to the UI. The only is the Client
I was using this pattern to create a bridge table, but it’s not working yet. The bridge table is ok, but still I cannot get what I need
Cost Actual per Account =
var Bridge_AccountClient =
SELECTCOLUMNS(
NATURALINNERJOIN ( 'Dim Account', 'Dim Client' ), "DimAccountID", 'Dim Account'[DimAccountID], "DimClientID", 'Dim Client'[DimClientID]
)
RETURN
CALCULATE(
SUM('Fact Financials'[CostActual] ),
FILTER(
'Dim Account',
CALCULATE( COUNTROWS( Bridge_AccountClient ) ) > 0
)
)
@Roberto Expanded tables only work for Many to 1 relationship, in this case you have a bi-directional filters between client and account, in this case filters are pushed into your code using CalculateTable_Vertipaq, the only natural table expansion here is between client and financials and also between financials, contract and GlobalSlicerPeriod .
Example of CalculateTable_Vertipaq pushed into the code when bi-directional filters are active:
Turn bi-directional filters off and it goes away and you are back again with table expansion:
Why don’t you use CROSSFILTER between Account and Client?
Hi @AntrikshSharma thanks for your fulfilling explanation. I cannot change anything in the model, unfortunately. I’ll give it a try with CROSSFILTER
Hi @Roberto, did the response provided by @AntrikshSharma help you solve your query? If not, how far did you get and what kind of help you need further? If yes, kindly mark the thread as solved. Thanks!
