Hi All,
New to the forums, and I have a very interesting scenario to solve for. Hoping to get some help or advice! I am building a report off a cube/dataset.
Ideally I would like to create the following SQL query in dax to solve for it:
FactTable1 = Can be used to define aggregations
FactTable2 = Can be used to define relationships from an attribute perspective but not from an actual relationship perspective
Dim1 = Dimension that is used to join both fact result sets. This dim field value exists on FactTable2 but has an incorrect relationship to FactTable2 if we used the actual relationship, This dim field value exists by the correct association to FactTable1.
FactTable2 needs to be filtered first to get the right fieldvalues to filter FactTable1 and we need some how break data lineage as well because FactTable2 has the wrong relationship but the right attributes to define a relationship.
I think this could be solved quite easily with a join in dax but I would have to evaluate all possible values for FactTable1 before doing an inner join to FactTable2 (Containing the correct filter results). So I am worried about performance there. Ideally I would do the SQL below in DAX:
SELECT
Dim.Field1,
Field2,
Field3,
SUM(Sales)
FROM FactTable1 AS fct
LEFT JOIN Dim1 AS Dim ON Dim.Field1 = fct.Field1
WHERE Field1
IN (
SELECT Field1
FROM FactTable2
WHERE Field1 = {Dynamic data values})
GROUP BY
Field1,
Field2,
Field3
In Dax I started with something like this, but data lineage screwed me up:
DEFINE
VAR __filter1 = Filterset
VAR __filter2 = Filterset2
VAR __FilterFactTable1 =
FILTER(
KEEPFILTERS(VALUES('FactTable1'[Field1])),
CALCULATETABLE (
VALUES('FactTable2'[Field1]), __filter1,__filter2
)
)
EVALUATE
SUMMARIZECOLUMNS(
Field1,
Field2,
Field3,
__FilterFactTable1,
"SumAmount",'FactTable1'[Sales]
)
Now this worked to an extent, I did not have to evaluate all the rows for FactTable1,
they were filtered by the rows defined in __FilterFactTable1.
However, data lineage screwed me up here since there IS a relationship but it is incorrect.
So some of the results are right but some are not.
Again, I cannot change the relationship unforunately! I can do an inner join here
but I believe I would have to evaluate all the results for FactTable1 before joining to the subset in FactTable2,
and I would have to break data lineage by adding "" or 0 to the key which will cost me performance too!
Ideally I would either be able to join with a where clause so I don't have to evaluate all results for
FactTable1 before limiting to the subset of FactTable2, and I think the above DAX gets me close
but now data lineage is screwing with the results!
