Forum Discussion

JemmaD's avatar
JemmaD
Icon for Helper V rankHelper V
1 year ago
Solved

Grouping rows in a table based on a matching value

I have a model which has a policy dimension (distinct policy reference) linked to two fact tables - 'Policy' and 'Location'

Both Policy and Location tables have multiple rows per policy reference, and when fields from each fact are added to a table using the dimension [POLICYREFERENCE] there are several rows per policy reference. This is expected. 

 

What I would like to do is use conditional formatting to group the policy references together by background colour so users can easily see the blocks which relate to the same policy. I have tried numerous measures and calculated columns found on this board, but they all exceed the memory - and I think that is because the table is using data from two different fact tables. 

I tried a RANKX in an attempt to get an alternate 1 and 0 for each unique policy reference but that isn't working.

 

Can you think of anything I can do? 

 

This is what I am after, like alternate colours based on some sort of rule:

 

Need Help
  • OwenAuger's avatar
    OwenAuger
    1 year ago

    Thanks for the update JemmaD !

    The POLICYREFERENCE values are sorted in the visual in lexicographic order which the ROWNUMBER function should also be using, so it is odd that the colours are not as expected!

     

    Idea #1:

    Could you try creating this Policy Index Debug measure and adding it to the table visual:

    Policy Index Debug =
IF (
    NOT ISEMPTY ( 'Policy' ) || NOT ISEMPTY ( 'Location' ),
    VAR _PolicyVisible =
        CALCULATETABLE (
            DISTINCT (
                UNION (
                    SUMMARIZE ( 'Policy', 'Dim Policy'[POLICYREFERENCE] ),
                    SUMMARIZE ( 'Location', 'Dim Policy'[POLICYREFERENCE] )
                )
            ),
            ALLSELECTED ()
        )
    VAR _PolicyIndex =
        ROWNUMBER ( _PolicyVisible, ORDERBY ( 'Dim Policy'[POLICYREFERENCE], ASC ) )
    RETURN
        _PolicyIndex
)

     This should return the index that is being used to determine the colour, so should help figure out where it's going wrong! The first POLICYREFERENCE should correspond to 1, then next to 2, and so on.

     

    The ISEMPTY tests are there so that the index is only returned for existing rows of the visual where either fact table is nonempty (hopefully).

     

    Other ideas:

    • The bidirectional relationships could be causing an issue of some sort. Do they need to be bidirectional or could they be converted to single directional?
    • Could you share a PBIX (dummy data if needed) and I could try to debug at my end.

    All the best

7 Replies

  • OwenAuger's avatar
    OwenAuger
    Icon for Super User rankSuper User
    1 year ago

    Hi JemmaD 

    I have attached a PBIX with one suggested method.

    In the PBIX, I have built a simple model containing the data in your screenshot, but it may well need tweaking to work in your actual model.

     

    The method I have used is create measure Row Colour to be used for conditionl formatting.

     

    The Row Colour measure:

    1. Creates a list of Policy Reference values that are visible in the visual.
    2. Uses ROWNUMBER to return the index (i.e. position) of the current policy number, assuming ascending order.
    3. If the index is odd, return one colour, otherwise return the other colour.

     

    Row Colour = 
-- Two alternating colours
VAR Colour1 = "#DAE9F8"
VAR Colour2 = "#C1F0C8"

-- This should be modified for multiple fact tables to include
-- all Policy Reference values visible in the visual.
VAR PolicyReferenceVisible =
    CALCULATETABLE(
        SUMMARIZE ( 'Fact', Policy[Policy Reference] ),
        ALLSELECTED ()
    )
-- Index for current Policy Reference
VAR PolicyIndex =
    ROWNUMBER (
        PolicyReferenceVisible,
        ORDERBY ( Policy[Policy Reference], ASC )
    )
-- Assign Colour1 to odd indices and Colour2 to even indices
VAR Result =
   IF ( ISODD ( PolicyIndex ), Colour1, Colour2 )
RETURN
    Result

     

    This measure is then used for background colour conditional formatting for each required field in the table visual, using Field value = Row Colour.

    When different filters are applied, the colours update as you would expect:

    The PolicyReferenceVisible variable would need adjusting based on your model if you have multiple fact tables.

    I'm thinking something like this:

     

    VAR PolicyReferenceVisible =
    CALCULATETABLE(
        DISTINCT (
            UNION (
                SUMMARIZE ( 'Fact', Policy[Policy Reference] ),
                SUMMARIZE ( 'Other Fact', Policy[Policy Reference] )
            )
        )
        ALLSELECTED ()
    )

     

    Note that if you include Row Colour (as written) as a visible field in the table visual, unwanted rows would be displayed. If it is just used for conditional formatting, this is fine, otherwise you would need to adjust the logic to return a value only when any of the fact tables are nonempty (or similar).

     

    Does something like this work for you?

    • JemmaD's avatar
      JemmaD
      Icon for Helper V rankHelper V
      1 year ago

      Hi OwenAuger 

      Thanks for putting so much effort into this! Much appreciated.

       

      I have tried to follow your instructions but the result is an error in the measure 'Calculation error in measure: ROWNUMBER's Relation parameter only contains columns added by DAX table functions. This is not supported.

      This is my syntax:

      Row Colour = 
VAR _Colour1 = "#DAE9F8"

VAR _Colour2 = "#C1F0C8"

VAR _PolicyVisible = CALCULATETABLE (
    DISTINCT (
        UNION (
            SUMMARIZE ( 'Policy' , Policy[POLICYREFERENCE] ),
            SUMMARIZE ( 'Location' , 'Location'[POLICYREFERENCE] ) ) ),
        ALLSELECTED() )

VAR _PolicyIndex = ROWNUMBER (
    _PolicyVisible,
    ORDERBY ( 'Policy'[POLICYREFERENCE], ASC ) )

VAR _Result = IF (
    ISODD ( _PolicyIndex ), _Colour1, _Colour2 )

RETURN
_Result

       

      Any ideas Owen?

      • OwenAuger's avatar
        OwenAuger
        Icon for Super User rankSuper User
        1 year ago

        You're welcome JemmaD !

        I see the problem:

        ROWNUMBER is throwing an error because _PolicyVisible ends up containing a single column with no lineage, which is not allowed in the relation argument of window functions such as ROWNUMBER.

         

        This comes about because the POLICYREFERENCE column references within each instance of SUMMARIZE (the 2nd argument of SUMMARIZE in this case) are not the same. This means lineage is lost when columns with different lineage are UNION-ed. Ideally they should both be the POLICYREFERENCE column from the Policy Dimension, if I have understood your model correctly 😉

         

        So the solution should be to update to something like this:

         

        Row Colour = 
VAR _Colour1 = "#DAE9F8"

VAR _Colour2 = "#C1F0C8"

VAR _PolicyVisible = CALCULATETABLE (
    DISTINCT (
        UNION (
            SUMMARIZE ( 'Policy' , PolicyDimension[POLICYREFERENCE] ), -- UPDATED
            SUMMARIZE ( 'Location' , PolicyDimension[POLICYREFERENCE] ) ) ), -- UPDATED
        ALLSELECTED() )

VAR _PolicyIndex = ROWNUMBER (
    _PolicyVisible,
    ORDERBY ( PolicyDimension[POLICYREFERENCE], ASC ) ) -- UPDATED

VAR _Result = IF (
    ISODD ( _PolicyIndex ), _Colour1, _Colour2 )

RETURN
_Result

         

         This will work as long as the PolicyDimension table has a 1-many relationship with each of 'Policy' and 'Location'.

         

        Can you get something like this working?

        If not, could you post a model diagram, or even share a link to a sanitised PBIX file?