paginated reports - decision tree - parameter control of another parameter

[garythomannCoGC]

How to return DAX table based on a condition? The IF function cannot return a table in DAX.

DAX How to return a table based on a condition - workaround for IF to return a table

Same problem and beautifully presented as below.  With a solution that works in daxstudio.  Party on :}

IF( 1=1, table_1, table_2 )

UNION(
   FILTER( table_1,     condition ),
   FILTER( table_2, NOT condition)
)

And now for the fun in pbi paginated reports builder.  Paginated Reports Bear where the .... are you?

So we have Council Org Plans Parm Choice parm that governs the Council Plan Outcome and Org Plan Theme parameters.

CouncilOrgPlansParmChoice parm dataset dax.  Four choices.  "Use.." means to drive off the Council Plan Outcome and Org Plan Theme parameters as normal.  The other three choices override the Council Plan Outcome and Org Plan Theme parameters.

CouncilOrgPlansParmChoice parm dataset dax

EVALUATE
DATATABLE (
    "Parameter Selectivity", STRING,
    {
        { "Use Council Plan and Org Plan parameters" },
        { "On On" },   -- Both 'All except NA'
        { "On Off" },  -- Council Plan 'All except NA'
        { "Off On" }   -- Org Plan 'All except NA'
    }
)

Council Plan Outcome and Org Plan Theme parameters have similar constructs.

CouncilPlanOutcome dataset dax

DEFINE
    -- parse the parameter value lists ready for the main querys
    VAR CouncilOrgPlansParmChoice = SUBSTITUTE (TRIM (SUBSTITUTE (@CouncilOrgPlansParmChoice, ",", "|")), "| ", "|" )

    -- create summary table
    VAR SummaryTableUnique = 
        -- distinct list of values, excluding 'blank's
        FILTER (
            DISTINCT (
                VALUES ( 'Measure Benefit Master'[Corp Plan Outcome] )
            ),
            NOT [Corp Plan Outcome] IN {
                "NA",
                "0",
                "Not Applicable",
                "Select",
                " ", 
                "",  
                BLANK()
            }   
        )
    -- add sorting column
    VAR SummaryTable = 
        ADDCOLUMNS (
            SummaryTableUnique, 
            "Sort", 1
        )

    -- create All row
    VAR AllTable =
        DATATABLE (
            "Corp Plan Outcome", STRING,
            "Sort", INTEGER,
            {
                { "All", 0 }
            }
        )

    -- create All except NA row
    VAR AllexceptNATable =
        DATATABLE (
            "Corp Plan Outcome", STRING,
            "Sort", INTEGER,
            {
                { "All except NA", 0 }
            }
        )

    -- create NA row
    VAR NATable =
        DATATABLE (
            "Corp Plan Outcome", STRING,
            "Sort", INTEGER,
            {
                { "NA", 0 }
            }
        )

    -- sandwich together
    VAR UnionTable1 = UNION ( SummaryTable, AllTable )   -- keep data lineage by doing SummaryTable first 
    VAR UnionTable2 = UNION ( UnionTable1, NATable )    
    VAR UnionTable3 = UNION ( UnionTable2, AllexceptNATable )    

    -- defaults shown determined by CouncilOrgPlansParmChoice 
    VAR Result = 
        UNION (
            FILTER ( UnionTable3,      LEFT(CouncilOrgPlansParmChoice,3) = "Use" ),   -- functions as normal
            FILTER ( AllexceptNATable, LEFT(CouncilOrgPlansParmChoice,2) = "On"  ),   -- override
            FILTER ( AllTable,         LEFT(CouncilOrgPlansParmChoice,3) = "Off" )    -- override           
        ) 

EVALUATE
    Result 

ORDER BY 
    [Sort] ASC,   -- sort All, NA first then all others
    [Corp Plan Outcome] ASC



/* testing framework */
<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">  
  <Parameter>
    <Name>CouncilOrgPlansParmChoice</Name>
    <Value xsi:type="xsd:string">On Off</Value>  
    <!-- test-: Use Council/Org Plan parameters, On On, On Off, Off On -->
  </Parameter>
</Parameters>

OrgPlanTheme dataset dax

DEFINE
    -- parse the parameter value lists ready for the main querys
    VAR CouncilOrgPlansParmChoice = SUBSTITUTE (TRIM (SUBSTITUTE (@CouncilOrgPlansParmChoice, ",", "|")), "| ", "|" )

    -- create summary table
    VAR SummaryTableUnique = 
        -- distinct list of values, excluding 'blank's
        FILTER (
            DISTINCT (
                VALUES ( 'Measure Benefit Master'[Org Plan Theme] )
            ),
            NOT [Org Plan Theme] IN {
                "NA",
                "0",
                "Not Applicable",
                "Select",
                " ", 
                "",  
                BLANK()
            }   
        )
    -- add sorting column
    VAR SummaryTable = 
        ADDCOLUMNS (
            SummaryTableUnique, 
            "Sort", 1
        )

    -- create All and NA rows
    VAR AllTable =
        DATATABLE (
            "Org Plan Theme", STRING,
            "Sort", INTEGER,
            {
                { "All", 0 }
            }
        )
    VAR NATable =
        DATATABLE (
            "Org Plan Theme", STRING,
            "Sort", INTEGER,
            {
                { "NA", 0 }
            }
        )

    -- create All except NA row
    VAR AllexceptNATable =
        DATATABLE (
            "Org Plan Theme", STRING,
            "Sort", INTEGER,
            {
                { "All except NA", 0 }
            }
        )

    -- sandwich together
    VAR UnionTable1 = UNION ( SummaryTable, AllTable )   -- keep data lineage by doing SummaryTable first 
    VAR UnionTable2 = UNION ( UnionTable1, NATable )    
    VAR UnionTable3 = UNION ( UnionTable2, AllexceptNATable )    

    -- defaults shown detirmined by CouncilOrgPlansParmChoice 
    VAR Result = 
        UNION (
            FILTER ( UnionTable3,      LEFT(CouncilOrgPlansParmChoice,3)  = "Use" ),   -- functions as normal 
            FILTER ( AllexceptNATable, RIGHT(CouncilOrgPlansParmChoice,2) = "On"  ),   -- override
            FILTER ( AllTable,         RIGHT(CouncilOrgPlansParmChoice,3) = "Off" )    -- override           
        ) 

EVALUATE
    Result 

ORDER BY 
    [Sort] ASC,   -- sort All, NA first then all others
    [Org Plan Theme] ASC



/* testing framework */
<Parameters xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns="urn:schemas-microsoft-com:xml-analysis">  
  <Parameter>
    <Name>CouncilOrgPlansParmChoice</Name>
    <Value xsi:type="xsd:string">On Off</Value>  
    <!-- test-: Use Council/Org Plan parameters, On On, On Off, Off On -->
  </Parameter>
</Parameters>

On report instantiation we have the correct values displayed for the governed parameters.  Which is correct.  Does not auto fire but at least correct values are defaulted for the user selection.

Change the governing parameter, correct values are defaulted but not checked?

So I suppose it is working to a point.  Only the relevant parameter values are displayed for the governing option.  The user has to manually select the choice and hit the View Report button.

Changing all the parameters properties from 'Automatically determine when to refresh' default to 'Always refresh' appears to have no effect.

The default values for the Council Plan Outcome and Org Plan Theme parameters do get overwritten and the drop down value list is restricted so I suppose this is all working :}

Any suggested improvements to make things a bit more funky for the user?  Would be nice if the report auto fired for the defaults.  This is one bug where the auto fire now does not work even though we have default values for all parameters.

================================================================================

Next bug.  So the report works but when we validate the dax dataset querys for the the governed parameters (Council Plan Outcome and Org Plan Theme parameters) we get the above error for both Council Plan Outcome and Org Plan Theme parameter datasets.

The parameter dataset properties are setup as per normal.  Even with the dataset not being validated the report does work.

Why the error on validation?

Come on Paginated Report Bear what is going on?

[Anonymous]

Hi,@garythomannCoGC .I am glad to help you.

According to your description, you have encountered the following two problems while designing a paged report. Here are my suggestions, I hope they will help you.
1. For the automatic refresh of the paging report.
You mentioned that you have done the following:

Changing all the parameters properties from 'Automatically determine when to refresh' default to 'Always refresh' appears to have no effect.

Could you please try turning on the Paginated Report Auto-Refresh button in the property bar on the right hand side of the Paginated Report Design page (and setting the refresh interval) to see if this solves the problem.

URL:
Power BI Paginated Reports June 2019 Feature Update Summary | Microsoft Power BI Blog | Microsoft Po...
 

2. For the error message:
“Could not update a list of fields for the query. Verify that you can connect to the data source and that your query syntax is correct. The query contains the 'CouncilOrgPlansParmChoice' parameter, which is not declared.”

You mentioned that the paging report displays the data and uses it properly, but at runtime, the system prompts you about the 'CouncilOrgPlansParmChoice' parameter, which is not declared.

Please check whether @'CouncilOrgPlansParmChoice' is properly mapped to the added report parameter when you use it, and also
Check the dependencies between the parameters to make sure they are configured correctly.

You can try querying with DAX Studio or other DAX debugging tools to make sure the syntax is correct and you can return the right results.

URL:DAX Studio | DAX Studio
 

For your idea and related attempts to use IF function and UNION function to realize IF function to control the filtering form, I think it is very good

IF function can not return the table directly, you cleverly bypassed this limitation by using UNION and FILTER functions to realize the effect of returning different tables based on conditions. This is a really great way of thinking.

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.

[garythomannCoGC · Accepted solution]

Hi @Anonymous,  thank you for your reply and suggestions.

Had a play with the AutoRefresh option and no luck for a solution.  I think it does make sense the way it is working where the user has to hit the View report button to fire.  The parameter default values are being triggered automatically which is great.

The CouncilPlanOutcome and OrgPlanTheme dataset error I have not found a solution but they do work despite the validation error.  Both have been created/tested via daxstudio.  I will leave for now as working.  No doubt the validation error will 'come out in the wash' later on :}