Forum Discussion
Matrix Rows with duplicate values
I have data on provider office visit codes and need to show utilization rates of those codes. The dataset includes a column for provider names and a unique identifier for providers called NPI. However, there are multiple providers with the same name that are being grouped and summed together in the matrix. For example, below "Olson" is actually two providers:
Now there are a couple of problems here.
- the two different providers with the same name are displayed in a single row by full name.
- these two providers' separate values are being summed together:
Their actual numbers are as follows:
So I would like to figure out
- how to show providers with the same name on different rows
- how to stop their numbvers being summed together
I assume the second problem with the summing has something to do with my data model and fields in queries:
I would much appreciate any help and/or suggestions.
It's odd to me that the setting is not available in the default UI that I could find. If you download Tabular Editor
https://www.sqlbi.com/tools/tabular-editor/
You can open it from your .pbix file under external tools.
Once you have it open you can select your column and set the property of 'Keep Unique Rows' to true.
You will also have to identify the column that is the unique identifier for the table. In my example it is Product_Key:
This will stop the visual from grouping them together.
7 Replies
It's odd to me that the setting is not available in the default UI that I could find. If you download Tabular Editor
https://www.sqlbi.com/tools/tabular-editor/
You can open it from your .pbix file under external tools.
Once you have it open you can select your column and set the property of 'Keep Unique Rows' to true.
You will also have to identify the column that is the unique identifier for the table. In my example it is Product_Key:
This will stop the visual from grouping them together.
I had high hopes but I the options you mention don't appear for me...
You might have to turn on the "unsupported" features. File > Preferences:
This doesn't work right yet because my key column has duplicates. Turns out as an artifact of some merged or appended queries I have duplicate provider ids, but they are not duplicate rows (see image below) so I need to start another thread (see link below) to get help with that issue then circle back here.
Remove specific, partially duplicate rows
In PowerQuery you can add an index column and assign that as the key on the table.
Sure, but I still need to get rid of those duplicates I think.
Yeah, those will almost certainly cause you problems down the road.
