question

IsmailCassiem-3967 avatar image
0 Votes"
IsmailCassiem-3967 asked IsmailCassiem-3967 commented

A Dimension with 2 facts on different grains

Good day,
i have received a request where i should only measureA where measureB has data

1 Dimension: Trip_TripNo
Fact MeasureGroup A: Count Trips
Fact MeasureGroup B: trip detail order qty

Mease A & B is the same source byt aggrageted at different levels, and the link to Trip DImension on Trip_No
I know i can enable unrelated measure=false
however the dm link is the same and i find Measue shows values where measureB has no data

i was thinking of working the count in measureB to have 1 measure but the count would be incorrect due to the detil of trp it includes

Any ideas please?

regards

sql-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.

1 Answer

CarrinWu-MSFT avatar image
0 Votes"
CarrinWu-MSFT answered IsmailCassiem-3967 commented

Hi @IsmailCassiem-3967,

Welcome to Microsoft Q&A!

You should create 2 dimensions to join 2 fact tables, because you said the same source aggregated at different levels. Please refer to this thread to get more details, see below:

Your dimension is represented by the surrogate key in whatever facts it applies to, so it should be the surrogate key that appears in fact 2.
I didn't spot the point of your question, originally, where you mentioned the 2nd fact was of a different grain. The dimensional way of doing this is to have a second dimension at that higher level, which only has columns appropriate to that grain. Both dimensions are 'conformed' in the sense that the titles and contents of the fields align.


Best regards,
Carrin


If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

· 9
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.

This seems multidimensional, so how about a little MDX along the lines of

 IIF ( IsEmpty ( measureB ), NULL, measureA )




0 Votes 0 ·

Thank You so much guys

@CarrinWu-MSFT > the user wants to use 1 dimension to filter on a trip but only have values where both MeasureB has detail
Please Note: there's another dimension depot that the user filters, so MeasureA does not pickup the depot filter hence why there's data on detail MeasureB but Measure A remains for all the trip

@Alexei > i will gives this a quick try


0 Votes 0 ·

Hi,
The mdx works to point but the grand ttoal still sums all count and at certain places where there is MeasureB then there's no measureA count

CREATE MEMBER CURRENTCUBE.[Measures].[Trip Count]
AS IIF((IsEmpty([Measures].[Order Volume Shipped]) OR [Measures].[Order Volume Shipped]=0),NULL,[Measures].[TripCount]),
FORMAT_STRING = "#,###",
NON_EMPTY_BEHAVIOR = { [Order Volume Shipped] },
VISIBLE = 1 , ASSOCIATED_MEASURE_GROUP = 'Trip Detail Measures';

0 Votes 0 ·

It now seems that the request is nor quite related to MeasureGroupA after all. The user seems in need of counting trips based on MeasureGroupB's data, and the fact that there's also some trip count in A is just misleading. So just forget A and count trips over B, with e.g. a distinct count measure.

0 Votes 0 ·

Thx so much
MeasureB has trip down to detail (item, customer etc) with 1 trip have 20 lines = count of 20, but its 1 trip which MeasureA gives
MeasureA = Header
MeasureB = Detail

PLZ Help, example of how to use distinct count?

0 Votes 0 ·

You mentioned that you had Trip_No column in your measure groups, and that's the column in B that you would base your distinct count measure on.

0 Votes 0 ·
Show more comments