question

Learner-5907 avatar image
0 Votes"
Learner-5907 asked LukasYu-msft edited

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

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?

sql-server-reporting-servicessql-server-analysis-services
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Learner-5907 avatar image
0 Votes"
Learner-5907 answered

Added a bridging table to resolve this issue.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LukasYu-msft avatar image
0 Votes"
LukasYu-msft answered LukasYu-msft edited

Hi,

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

· 2
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi Lukas, Thanks for your solution but it didn't work.

Finally added a bridging table to resolve this issue.

0 Votes 0 ·

Thanks for you feedback! Cheers for solving the problem.

P.S. The solution is provide by DarrenGosbellm, not me. I am just checking the case progress see if it has been solved or what more we could do. You could mark your solution as answer or above Darren's post, see which resolves your issue at best level.

0 Votes 0 ·
DarrenGosbell-0123 avatar image
0 Votes"
DarrenGosbell-0123 answered

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.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.