Dynamic change for totals in table visual based on field Parameters selection

[Yasoda_Sathulur]

Acutually we are migrating MSGR reports in Power BI. In MSGR based on prompt selection source of totals will change, But in Power Bi we don't don't have that option.
In Power BI we have only one fact table using field parameters showing totals in table view.
In MSGR we have multiple prompts if i selected only two prompts it will collect totals from fact table and if i selected multiple prompts it will collect totals from view(combination of multiple fact tables) table.

How can i resolve this issue, is there anyone to help this?

[v-hashadapu]

Hi @Yasoda_Sathulur , Hope you're doing fine. Can you confirm if the problem is solved or still persists? Sharing your details will help others in the community.

[v-hashadapu]

Hi @Yasoda_Sathulur , Thank you for reaching out to the Microsoft Community Forum.

We find the answer shared by @Praful_Potphode  is appropriate. Can you please confirm if the solution worked for you. It will help others with similar issues find the answer easily
Thank you @Praful_Potphode  for your valuable response.

[Praful_Potphode · Accepted solution]

Hi @Yasoda_Sathulur ,

please provide more information on this.usually any programming language can handlEvery prompt can become a slicer selection and based on that IF/SWITCH can be written.Below is an example of IF statement usage.

Total Contacts =
VAR SelectedPromptsCount =
    -- Counts how many unique prompt names are currently selected/filtered.
    -- Assuming 'Prompt Dimension' is the table used for the user slicer/filter.
    COUNTROWS( ALLSELECTED('Prompt Dimension'[Prompt Name]) )

VAR FactTableResult =
    -- Calculation for 'two prompts selected' (simple fact table aggregation)
    -- Replace 'fact_table' and 'ContactKey' with your actual table and key
    CALCULATE(
        SUM('fact_table'[ContactCount]), -- Use SUM for pre-aggregated fact tables, or COUNTROWS if counting rows
        ALLSELECTED() -- Ensure the calculation respects external filters but not the prompt filter if needed
    )

VAR ViewTableResult =
    -- Calculation for 'multiple prompts selected' (the complex view logic)
    -- This section needs to replicate the logic of your MSGR view (multi-fact combination).
    -- This will often be a more complex formula combining multiple facts.
    CALCULATE(
        SUM('View_Table'[CombinedContactCount]) -- Example from a combined View table
    )

RETURN
    -- Implement the logic: If SelectedPromptsCount <= 2, use the simple FactTableResult.
    -- If SelectedPromptsCount > 2 (i.e., multiple prompts), use the ViewTableResult.
    IF(
        SelectedPromptsCount <= 2,
        FactTableResult,
        ViewTableResult
    )

 Please give kudos or mark it as solution once confirmed.

Thanks and Regards,

Praful

[Pragati11]

Hi @Yasoda_Sathulur 

In Power BI semantic model you can have more than 1 fact table.

[vojtechsima]

hey, @Yasoda_Sathulur ,

Well, your question can mean tons of things.

As you said, field parameters can help you achieve that, where you define what 'prompts' do and what calculations. Or you can build dynamic measures that will do similar stuff. You just need to define the results.

If you had some example or something, I would probably construct a more specific answer.

[ajaybabuinturi]

Hi @Yasoda_Sathulur,

As far as I know parameters should work for totals. Can you please provide the sample data of .pbix file using G-Drive or some other ways. So that I can able to provide the exact solution.

Thanks,
If you found this solution helpful, please consider giving it a Like👍 and marking it as Accepted Solution✔. This helps improve visibility for others who may be encountering/facing same questions/issues.