Model composite with seperate table

[Sophie__]

Hello,

I have a composite model with a FACT table in direct query and dimension tables in dual connection.

The direct query model works correctly when using dimension data with a calculation from the FACT table.

However, when I use a parameter table in a DAX that is not connected to the model, the filters on the direct query are no longer applied because I have a limit of 1 million rows which prevents the matrix from being displayed.

Solde =

VAR variation =

    SELECTEDVALUE ( Dim_variation[variation_key] )

VAR result =

    SWITCH (

        variation,

        "MTD", [Solde],

        "QTD",

            CALCULATE (

                [Solde],

                DATESINPERIOD ( 'Dim_Time'[date_key], MAX ( 'Dim_Time'[date_key]), -3, MONTH )

            ),

        "HTD",

            CALCULATE (

                [Solde],

                DATESINPERIOD ( 'Dim_Time'[date_key], MAX ( 'Dim_Time'[date_key] ), -6, MONTH )

            ),

        "YTD", CALCULATE ( [Solde], DATESYTD ('Dim_Time'[date_key] ))

    )

RETURN

    result

Why does the composite model remove the filters when using a SWITCH or IF statement in a DAX with a parameter table?

Thanks for your answer

Sophie

[Sophie__]

Hello @MFelix ,

the filters affecting the matrix are the date and an account.

I have two dimensions in the matrix: a measure without a switch on the parameter table => no problem, but when I add a measure with the DAX on the parameter table => error message exceeding 1,000,000.

Sophie

[MFelix]

Are you also adding the Variation key to your table or is it based on a slicer?

[Sophie__]

@MFelix it's only based on the slicer

[amitchandak]

@Sophie__ , you can check what queries are and how they different using performance analyzer https://learn.microsoft.com/en-us/power-bi/create-reports/performance-analyzer
I would suggest trying out Field Parameter as suggested by @MFelix . Or calculation group.

[MFelix]

Hi @Sophie__,

I have similar models with disconnected tables and if expressions and did not get this type of issues, but me mindfull that each model as it own specifications.

How are you setting up the matrix? What are the filters that impact the matrix and the calculations?

Have you tried creating a Field parameter or a Calculation Group instead of an IF statement?

[Sophie__]

Hi @MFelix ,

Power BI splits the query in two when I add a specific field; I don't understand why, and it's this query that returns over 1 million rows.

Sophie

[MFelix]

Hi @Sophie__ ,

Is that specific field comming from what table?

[Sophie__]

Hi @MFelix , 

This is a specific field that comes from a dimension table.

A second query is launched directly:

SELECT `je_header_description`,

`je_line_description`
FROM `fco-data-gsp-products-dev`.`profit_and_lost_exp_dev`.`dmt_dim_gl_v`
GROUP BY `je_header_description`,

`je_line_description`
LIMIT 1000001 OFFSET 0

This query retrieves over 1 million rows.

I don't understand why i have this second request.

Regards,

Sophie

[MFelix]

Apologies for the additional questions.

But what type of relationship is this? I see in your image that you have a many-to-many relationship but not sure what is the table.

What is this dimension role in the information you are getting?

Do you only have one period selected or do you show all of the periods at the same matrix?

[v-achippa · Accepted solution]

Hi @Sophie__ ,

Thank you for reaching out to Microsoft Fabric Community.

Thank you @amitchandakand @MFelix for the prompt response.  

As we haven’t heard back from you, we wanted to kindly follow up to check if the response provided by the user's for the issue worked? Is your issue resolved?

If not please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.

https://community.fabric.microsoft.com/t5/Power-BI-Community-Blog/How-to-provide-sample-data-in-the-...

Thanks and regards,

Anjan Kumar Chippa

[v-achippa]

Hi @Sophie__ ,

We wanted to kindly follow up to check if the response provided by the user's for the issue worked? Is your issue resolved?

If not please provide sample data in a usable format.
Please show the expected outcome based on the sample data you provided.

Thanks and regards,

Anjan Kumar Chippa