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
Rawat2203
New Member

How can we make the dimensional model respond the same as the columnar model?

image (7).pngimage (6).pngIntroduction

We look at a dummy dataset returned from a customer survey. It has questions, the potential answers for each questions, the respondents to the survey, and the actual answers by the respondents to the survey. We have nicely modeled this into a star fact-dimension model. However we run into a challenge with this model.

 

Challenge

With the dimensional model we have a fact and three dimension tables:

fct_responses, dim_respondent, dim_question and dim_answer.

 

However with this setup it is not possible to filter the responses across questions. For example, by clicking Answer A for Question 1 in the bar chart, filters out everything for Question 2 and 3. But what we want to see is how these two respondents: James and John answered question 2 (B, A) and question 3 (C, C).

 

In the columnar model on the right, this is visible by selecting A in the Question 1 chart.

 

Question: How can we make the dimensional model respond the same as the columnar model?

 

 

1 ACCEPTED SOLUTION
v-eqin-msft
Community Support
Community Support

Hi @Rawat2203 ,

 

As far as I know, your calculation is based on your data model. It is hard for dimensional model to work as columnar model directly. I suggest you to add an unrelated dimtable to help your calculation.

DimTable =
SUMMARIZE (
    Fact2,
    Fact1[question_id],
    Fact2[question_text],
    Fact1[answer_id],
    Fact2[answer]
)

Data model:

veqinmsft_0-1685001554523.png

Filter measure:

Filter = 
VAR _SELECTQUESTION =
    SELECTEDVALUE ( DimTable[question_id] )
VAR _SELECTANSWER =
    SELECTEDVALUE ( DimTable[answer_id] )
VAR _RESPONDENT =
    CALCULATETABLE (
        VALUES ( Fact1[respondent_id] ),
        FILTER (
            ALL(Fact1),
            Fact1[answer_id] = _SELECTANSWER
                && Fact1[question_id] = _SELECTQUESTION
        )
    )
RETURN
    IF ( MAX( Fact1[respondent_id] ) IN _RESPONDENT, 1, 0 )

Add this measure into the visual level filter in your visuals and set it to show items when value = 1.

Result is as below.

veqinmsft_1-1685001641643.png

 

Best Regards,
Rico Zhou

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

View solution in original post

1 REPLY 1
v-eqin-msft
Community Support
Community Support

Hi @Rawat2203 ,

 

As far as I know, your calculation is based on your data model. It is hard for dimensional model to work as columnar model directly. I suggest you to add an unrelated dimtable to help your calculation.

DimTable =
SUMMARIZE (
    Fact2,
    Fact1[question_id],
    Fact2[question_text],
    Fact1[answer_id],
    Fact2[answer]
)

Data model:

veqinmsft_0-1685001554523.png

Filter measure:

Filter = 
VAR _SELECTQUESTION =
    SELECTEDVALUE ( DimTable[question_id] )
VAR _SELECTANSWER =
    SELECTEDVALUE ( DimTable[answer_id] )
VAR _RESPONDENT =
    CALCULATETABLE (
        VALUES ( Fact1[respondent_id] ),
        FILTER (
            ALL(Fact1),
            Fact1[answer_id] = _SELECTANSWER
                && Fact1[question_id] = _SELECTQUESTION
        )
    )
RETURN
    IF ( MAX( Fact1[respondent_id] ) IN _RESPONDENT, 1, 0 )

Add this measure into the visual level filter in your visuals and set it to show items when value = 1.

Result is as below.

veqinmsft_1-1685001641643.png

 

Best Regards,
Rico Zhou

 

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.