Forum Discussion
Calculation Groups with respect to dimension
Hi Team,
We have fact and dimension and want to re-use same measure across different dimensions. Can you please tell if there is way to achieve in DAX?
For Example:
Consider, We have Fact Table
| DateId | ProductId | CustomerId | RegionId | IndustryId | Amount |
| 20220501 | A | xyz | 2 | 4 | 100 |
Above Fact is related to corresponding Dimension Tables ( Product, Customer,Region,Industry)
We want to calculate measures as below
[Dimension] Count -- distinct values in Fact Table for corresponding dimension (ex: count(distinct ProducId)
[Dimension] MoM # -- Based on [Dimension Count]
Example: We have solution to create each measure seperately as below but looking is there any option to reuse the single measure across different dimensions ( similar to calculation groups i.e SELECTEDMEASURE() , do we have concept like SELECTEDDIMENSION()).
Please suggest if there are better ways to solve below problem
we are planning to create measures as below
Product Count
Product MoM#
Customer Count
Customer MoM #
Industry Count
Industry MOM #
Region Count
Region MOM #
Thanks,
Abhiram
Hi abhiram342 ,
This post may help you: Introduction to Tabular Editor for Business Users - Excelerator BI
1. For "distinct values in Fact Table for corresponding dimension", you can try to modify the script below to meet your scenario to generates measures on every selected column to create counts.
2. For MOM based on the measures above, change the script below to meet your requirements:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 Replies
As long as you have one-to-many relationships from your dimension tables to your fact table then any measures should work across all dimensions. Just put the appropriate column from your dimension table into a visual with the measure, or use the dimension table columns as filters, and it should just work.
Hi abhiram342 ,
This post may help you: Introduction to Tabular Editor for Business Users - Excelerator BI
1. For "distinct values in Fact Table for corresponding dimension", you can try to modify the script below to meet your scenario to generates measures on every selected column to create counts.
2. For MOM based on the measures above, change the script below to meet your requirements:
Best Regards,
Icey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
