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, a...
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

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?

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

    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's avatar
      OwenAuger
      Icon for Super User rankSuper User
      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

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

        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 😁