Forum Discussion

jjkiernanNGDS's avatar
jjkiernanNGDS
Advocate I
3 years ago

Field Parameters - Returned using IF or Switch

Hi, I have a slicer which has a table of field parameters. I have a situation, that in a particular case, I want to show additional fields on the field parameter when a specific value is selected ( in my code below, a selected company code) I.E when that company code is selected there are 6 field parameters , otherwise only 4. I have tried this using a simple IF statement and also with Switch. I get the same error - Expression refers to multiple columns. Multiple columns cannot be converted to scalar values. I understand I'm trying to return a table, thats what i want to do. 

Is there any method to have the table return in the DAX. I'm assuming not, but just in case there's a means of doing this. 

Category Selection Fields =
VAR TableA= {    ("Customer", NAMEOF('DimCustomers'[NAME]), 0),
    ("Country", NAMEOF('DimCustomers'[COUNTRY]), 1),
    ("Sales Rep", NAMEOF('SalesPersons'[SALESMAN_NAME]), 2),
    ("Product Group", NAMEOF('ProductGroups'[Product Category]), 3)
}
VAR TableB = {    ("Customer", NAMEOF('DimCustomers'[NAME]), 0),
    ("Country", NAMEOF('DimCustomers'[COUNTRY]), 1),
    ("Region", NAMEOF('FCTOrders'[REGION_CODE]), 2),
    ("Sales Rep", NAMEOF('SalesPersons'[SALESMAN_NAME]), 4),
    ("Product Group", NAMEOF('ProductGroups'[Product Category]), 5),
    ("District", NAMEOF('FCTOrders'[DISTRICT_NAME]), 3)
}

RETURN IF(SELECTEDVALUE(GroupCompanies[Company_Code])=203,TableB,TableA)
 

 

Need Help

7 Replies

    • jjkiernanNGDS's avatar
      jjkiernanNGDS
      Advocate I
      3 years ago

      Thanks for coming back, however i am trying to make the Field Parameters Dynamic using a standard selected slicer value, I'm not trying to find selected Field Parameter value either. 

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    Did you ever solve this looking to do the same thing?

    • jjkiernanNGDS's avatar
      jjkiernanNGDS
      Advocate I
      2 years ago

      I didn't solve the issue in this particular way. And designed my report in a different manner. However, I recently seen a Video by Bas on How to Power BI, which uses Disconnected tables and some measures, to determine which Items to show in a slicer which would have worked for my use case. 

      https://youtu.be/NqlgTXCfqSs?si=COG1C6YbFIlVIMoP

      • viraj_arsekar's avatar
        viraj_arsekar
        Helper I
        1 year ago

        Thanks for sharing. I was able to use the logic in this video to achieve what I needed. Only thing it got much complicated, would have been so simple if IF or SWITCH worked in the DAX for parameter.

  • viraj_arsekar's avatar
    viraj_arsekar
    Helper I
    1 year ago

    I'm trying to do the exact same thing. My code looks very much similar to this. Select 4 paramerts if "Yes" is selected in one of the slicers, or select 6 parameters. Using this paramet in the table cloumns. Table will show either 4 or 6 columns depending on what is selected in the slicers. It looks so simple, but IF and SWITCH do not work, it gives me the same erros. I wish it worked, would have been amazing to do such conditional displaying of columns and parameters. Tried many things, no luck.

  • titusissam's avatar
    titusissam
    Frequent Visitor
    1 year ago
    Hi there, Good Day..

    I tried this work around by adding a new column to field parameter and used the newly added column to my main table slicer.. this way I was able to control my Parameter values.
     
    Just tried this and felt it worked for me, but I havent tested this extensively. your thoughts here is appreciated.

    {    (    "Customer", NAMEOF('DimCustomers'[NAME]), 0, "Table1"),
        ("Country", NAMEOF('DimCustomers'[COUNTRY]), 1, "Table1"),
        ("Sales Rep", NAMEOF('SalesPersons'[SALESMAN_NAME]), 2, "Table1"),
        ("Product Group", NAMEOF('ProductGroups'[Product Category]), 3, "Table1"),
     
    ("Customer", NAMEOF('DimCustomers'[NAME]), 0, "Table2"),
        ("Country", NAMEOF('DimCustomers'[COUNTRY]), 1, "Table2"),
        ("Sales Rep", NAMEOF('SalesPersons'[SALESMAN_NAME]), 2, "Table2")
    }