Azure SSAS Tabular Model - Fact table - Fact Table filter with different granularity

Question

Hi All, In our Tabular model we got Claim_Order fact & Payment fact table. Linking between 2 fact is by Claims dimension.

Relationship: 1 claim can have many Payments.

All was fine until we got a requirement in which we need to list down all Claim_order and if there is any payment show the payment related details.(left join in simple SQL from Claim to Payment)

So for example we got 100 claim_order and out of which only 50 has paid, then our report should show all 100 claims along with payments details. Payment details are coming from Payment Fact and related payment dimensions.

We use Paginated report to generate this reporting requirement but when we start adding fields related to payment, it auto filters claims that has only a payment present in the Payment fact.

Any solution for this ? Do we need dummy payment record for the claims which doesn't have a payment in the Payment fact or DAX can solve this issue?

Answer 1

You should be able to fix this by including a measure based on the Claim table. My suggestion would be to create a measure like the following:

[Claim Count] := Countrows('Claim')

then include this in your query. In this way you should get at least one row for each claim plus extra rows for each related payment (assuming you also have payment related measures in your query). This measure should return 1 for every row, but since you are using a paginated report you can just not use this column in your report.

The reason this works is that queries against tabular models typically only return rows where the measures return non-empty values. So if you only have payment related measures in your query only claims that have payments will be returned.

Answer 2

Hi,

Have your solved the problem ? Could we know your current progress?

Answer 3

Added a bridging table to resolve this issue.