Issue making calculated table dynamic with parameter/lookup table...any and all suggestions welcome

[ajjava]

I'm hoping to get some insight on an issue I'm having with using a parameter/lookup table selection value used in the creation of calculated table.

Scenario:

• DATA SOURCE is a flat file from Excel, containing claims data.
• There is one row for each FILE NUMBER (claim), with several columns of related facts for each claim
• Due to the relatively small size of the typical DATA SOURCE file, the choice has been made to forego a star schema model

Snapshot of DATA SOURCE file

Goal:

• I'd like the selection of the desired FACT COLUMN to be dynamic, based on the value passed by a parameter/lookup table (and related measure that stores the value of the selected FACT COLUMN)
• The resulting calculated table should then list all of the possible values in the selected FACT COLUMN
• Aggregate those rows, to ultimately determine the TopN + All Others for said FACT COLUMN
• I'm omitting a few other details that make this aggregation much more complex than it might appear here. Suffice it to say that the current logic is returning the desired results. I'm just trying to limit the number of calculated objects needed to get the desired result, by making those calculated objects as dynamic as possible

Issue:

• You'll see in the images below that when I pass the parameter selection value to the calculated table, no results are returned
• BUT, if I instead hard-code the parameter value, the correct results are returned

I've debugged every individual piece of the related measures/tables and the results are correct. I cannot for the life of me figure out why the parameter selection won't work, but hard-coding will. Any other ideas/suggestions on how to address?

Param/Lookup Table:

Measure to store param selection:

Circled part is the piece that I want changed dynamically:

Results with hard-coded param value:

Results with dynamic param selection:

[johnt75 · Accepted solution]

Calculated columns and tables are only calculated during data load or refresh, they pay no attention to slicers or filters. If you want something to be dynamic you have to use a measure.

[Anonymous · Accepted solution]

Hi,johnt75,thanks for your concern about this issue.

Your answer is excellent!
And I would like to share some additional solutions below.

Hello,@ajjava .I am glad to help you.

In your case, I think your subsequent ideas are correct:

1. By creating a virtual table in measure instead of the real existing calculated table, you can realize the effect of passing the filtered parameters dynamically (parameters in measure)
Use the filtered virtual table as a condition for subsequent calculated tables
Here is my test.

I used the vlaues function to create a table that displays the parameter values individually and added it to the slicer as a filtered parameter value.
The results in the table are dynamically displayed by the value of MEASURES to indirectly filter the data (you can also use the filtered data for subsequent calculations)

It is not feasible to use DAX directly in Power BI Desktop to implement dynamic filtering of calculated tables. You mentioned that the code works when you try to use hardcoding, but it is not possible to pass dynamically changing parameter values.

Calculated table/calculated column properties are static, it can't interact with filters on the visualization page, so it can't satisfy the need for dynamism, their values change only when you refresh/create the calculated table or calculated column (so calculated tables and calculated columns take up memory, they are real existent static data, whereas MEASURES are virtual table dynamic data)
Measure can interact dynamically, but it can only return a single value.

So using a measure or calculated table in Desktop does not dynamically return a table (which is the reason for your problem).
So if you want to implement the use of measure to dynamically filter out table results
Suggestion:
Create a virtual table in measure and then use the virtual table to calculated or labeled data.

Set measure = “The markers you set” in the filter field.

I recommend using a slicer to pass parameter values (create a new parameter table, do not create relationships to avoid direct filtering)

The following filtering logic creates multiple virtual tables and supports slicers passing multiple parameter values

Responsible for the projects = 
VAR _userNameList =
    VALUES ( UserInfo[UserName] )
VAR _userIDList =
    CALCULATETABLE (
        VALUES ( 'User'[UserID] ),
        FILTER ( ALLSELECTED ( 'User' ), 'User'[UserName] IN _userNameList )
    )
VAR _projectInfo =
    CALCULATETABLE (
        VALUES ( 'Project'[ProjectID] ),
        FILTER ( ALLSELECTED ( 'Project' ), 'Project'[UserID] IN _userIDList )
    )
RETURN
    IF ( MAX ( 'Project'[ProjectID] ) IN _projectInfo, 1, 0 )

2. If you only want to show the final filtered table, you can try to use the metrics of the final table data to be filtered out into a string (using the CONCATENATEX function), and will be displayed as a result (after the connection into a string, many subsequent calculations can not be carried out, such as summing, averaging, etc., you can only show the final results of the table filtered in the card)

CONCATENATEX function (DAX) - DAX | Microsoft Learn
 

I hope my suggestions give you good ideas, if you have any more questions, please clarify in a follow-up reply.
Best Regards,
Carson Jian,
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[ajjava]

Is a possible solution to change the calculated table into a virtual table, inside subsequent expressions (expressions not shown in my original post)? I'm EXTREMELY new to PBI, coming over from a Tableau background.

[johnt75 · Accepted solution]

Calculated columns and tables are only calculated during data load or refresh, they pay no attention to slicers or filters. If you want something to be dynamic you have to use a measure.