Grouping rows in a table based on a matching value

[JemmaD]

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:

[OwenAuger]

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]

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]

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?

[JemmaD]

@OwenAuger 

Thanks once again - your explanation is very helpful for me to learn. 

The measure is now operating as i've changed the SUMMARIZE to the dimension, but strangely it's not alternating the colours by policy reference consistently. It's done the first two references right, then it's grouped the next four policy references under one colour.

You're correct in your assumption about the policy dimension - it's a very simple model:

And the table contains POLICYREFERENCE from Dim Policy, then complementary fields from both the facts. This is the result when I apply field value = Row Colour:

[OwenAuger · Accepted solution]

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

[JemmaD]

Ah. Yes that de-bug has helped to understand, this is what is happening:

This is because the Policy dimension has many more policy references compared to the Location table. Adding the debug measure to both the Policy fact table and the Policy dimension shows the debug measure starting at 1 and increasing by 1 each time. 

When I add the debug measure to the Location fact table, it starts at 24,806 as this is the first policy it finds which matches to the dimension. 

I then forced ALL the policy references from Dim Policy into the Location table - just with empty columns for those which are not found, and the debug measure works fine.

And then in the visual table, if I remove the null entries from the location table the debug column seems to count consecutively from 1 colours are working:

So we got it working! Thank you SO much for your help on this Owen 😁

[OwenAuger]

Nice work! So glad you got it working 🙂

Glad to be of some assistance!

All the best.

Owen