Forum Discussion

richard_wylde's avatar
richard_wylde
Icon for Helper IV rankHelper IV
7 years ago
Solved

FIRSTNONBLANK not working


I've used the functions SWITCH(FIRSTNONBLANK( to successfully give the user a choice of measures to display, however I'm unable to do the same for dimensions.

 

I've created a table called Which_Dimension with one column called Dimension_Name & populated it with the names of the dimensions. I can display this & successfully highlight each value. However when I create a the following column & apply this to the Axis it only choses the top value in the Dimension_Name, regardless which value is highlighted :-

 
Selected Dimension (FirstNonBlank) = SWITCH( FIRSTNONBLANK( Which_Dimension[Dimension_Name], Which_Dimension[Dimension_Name] )
,"INSURER NAME",Query1[INSURERNAME]
,"POLICY TYPE",Query1[POLICYTYPE_CODE]
,"PROCESS TYPE",Query1[PROCESSTYPE_CODE]
,"RESULT STATUS",Query1[RESULT_STATUS_CODE]
,"SOURCE",Query1[SOURCECM_DESC]
)

Any idea what I'm doing wrong ?

Thank you

Need Help
  • OwenAuger's avatar
    OwenAuger
    7 years ago

    Hi richard_wylde 

    Calculated columns are computed at report refresh, and don't respond to filters in the report.

     

    So unfortunately you can't have a calculated column that updates based on slicers or other filters in the report (unlike some other tools out there).

     

    However, some other ways of achieving the same effect are:

    1. Create a table containing attribute-value pairs, where the attributes would be all possible dimensions (INSURER NAME, POLICY TYPE,...). Something similar to this post, but the setup may be a little different since we can now use many-to-many and bidirectional relationships in Power BI.
    2. Use bookmarks to change the axis dimension as in this post.

    Please post back if needed. A sample pbix or sample data would help to give a more concrete solution.

     

    Regards,

    Owen

2 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    7 years ago

    Hi richard_wylde 

    Calculated columns are computed at report refresh, and don't respond to filters in the report.

     

    So unfortunately you can't have a calculated column that updates based on slicers or other filters in the report (unlike some other tools out there).

     

    However, some other ways of achieving the same effect are:

    1. Create a table containing attribute-value pairs, where the attributes would be all possible dimensions (INSURER NAME, POLICY TYPE,...). Something similar to this post, but the setup may be a little different since we can now use many-to-many and bidirectional relationships in Power BI.
    2. Use bookmarks to change the axis dimension as in this post.

    Please post back if needed. A sample pbix or sample data would help to give a more concrete solution.

     

    Regards,

    Owen