Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
jPinhao
Helper II
Helper II

Ambiguity in similar measures across multiple Fact tables

In our model we have 2 Fact tables:

* Fact_UserAction - user performed an action in the software

* Fact_FeatureUsed - user used a feature within the software

 

These are 2 separate facts as a user action might actually represent multiple features being used. Eg. Plotting a line on a chart would create:

* 1 row in Fact_UserAction - LinePlotted

* 3 rows in Fact_FeatureUsed - Chart Lines, Charts, Data Visualisations

 

Across both tables, we'd like to measure common metrics like # of Users, # of Sessions, # of Projects, etc. :

 

duplicate_measures.png

 

The first issue is that these measures can't have duplicate names across different tables, which means we'd have to have slightly different names like: #Users[Actions]; #Users[Features]

 

The second is to do with ambiguity. When reporting, or more relevantly, when using the Service smart Q&A system, typing #Users would show multiple options, and picking the wrong one would obviously lead to meaningless visualizations being reported.

 

For us self-service is quite important - we really want to create a model where it is easy to find the right answers. Ambiguity in measures could really hurt this, and I was wondering if there's an elegant solution for this. The only thing I could think of is have separate fact tables for each of the different measures, eg: SessionRun table to measure sessions (with actions and features used during the session as dimensions), ProjectUsed (again, dimensions), etc. This adds complexity to the model (bridge tables between session/project and all actions; extra storage for keeping these, etc.), and it would mean that we'd have to almost make up fact tables for every new measurable entity we're interested in - this doesn't sound right?

 

Is there a simpler solution to this? Is having repeated measures across facts tables the way to go, and hope people figure out the right one to use?

3 REPLIES 3
v-qiuyu-msft
Community Support
Community Support

Hi @jPinhao,

 

The first issue is that these measures can't have duplicate names across different tables, which means we'd have to have slightly different names like: #Users[Actions]; #Users[Features]


 

Based on my test in Power BI Desktop version 2.40.4554.463, it's not supported to create two same name measures in different tables. It will throw the error like below:

 

w2.PNGw1.PNG

 


The second is to do with ambiguity. When reporting, or more relevantly, when using the Service smart Q&A system, typing #Users would show multiple options, and picking the wrong one would obviously lead to meaningless visualizations being reported.

 

For us self-service is quite important - we really want to create a model where it is easy to find the right answers. Ambiguity in measures could really hurt this, and I was wondering if there's an elegant solution for this. The only thing I could think of is have separate fact tables for each of the different measures, eg: SessionRun table to measure sessions (with actions and features used during the session as dimensions), ProjectUsed (again, dimensions), etc. This adds complexity to the model (bridge tables between session/project and all actions; extra storage for keeping these, etc.), and it would mean that we'd have to almost make up fact tables for every new measurable entity we're interested in - this doesn't sound right?

 

Is there a simpler solution to this? Is having repeated measures across facts tables the way to go, and hope people figure out the right one to use?


 

As far as I know, Q&A feature relies on the names of the tables, columns, and calculated fields in the underlying dataset to find the answer. In my opinion, the measure will not affect the Q&A results, you needn't to worry about this. I would suggest you to take a look at below links to know how Q&A works with underlying dataset:

 

How to use Power BI Q&A

Make your data work well with Q&A in Power BI

Tips for asking questions in Power BI Q&A

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

It's good to see from those pages that Q&A does some effort in understanding the context you're asking the question in, that might make it slightly better. However they do talk about the issue with measures with similar names becoming ambiguous and potentially leading the user to ask the wrong question.. I was wonering if there was some way to model the data such that you wouldn't need these similarly named measures, but my guess is not 😞

Hi @jPinhao,

 

If you are worry about the similar measures affect the end user type proper question in Q&A, you can create some featured questions to this dataset, and these questions will be available to users when they come to Q&A. See: Create featured questions for Power BI Q&A.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.