Forum Discussion
Helper VGrouping rows in a table based on a matching value
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
Helper V
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:
Super UserThanks 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