This browser is no longer supported.
Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(281.6, 39%, 37%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EIC%3C/text%3E%3C/svg%3E)
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
Hi @Ismail Cassiem ,
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.
![](data:image/svg+xml, %3Csvg xmlns='http://www.w3.org/2000/svg' height='64' class='font-weight-bold' style='font: 600 30.11764705882353px "SegoeUI", Arial' width='64'%3E%3Ccircle fill='hsl(32, 95%, 13%)' cx='32' cy='32' r='32' /%3E%3Ctext x='50%25' y='55%25' dominant-baseline='middle' text-anchor='middle' fill='%23FFF' %3EAS%3C/text%3E%3C/svg%3E)
This seems multidimensional, so how about a little MDX along the lines of
IIF ( IsEmpty ( measureB ), NULL, measureA )
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
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';
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.
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?
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.
Please Help?
The distinct count gave me all sorts of trouble as it was not flexible for other dimensions/filters
i ended up playing with CTE/View with rownumber partition split all records, it gives count per line but al least rolled up & filtered i get the proper count.
just looks funny on low level
Thank You very much Guys for helping me
We must have not heard some further requirements, as while distinct count in MD has its cons, and quirks, it does play nice with related dimensions. Well, as long as you got your issue solved :)