I have a matrix whose columns are a subset of the rows in table, which is accomplished by filtering that table by one of it’s fields. This works great when I use a value source from a related table. However, when I add a second unrelated value, my filtering no longer works and all columns are displayed.
I think I understand why this is happening as the DAX generated looks roughly like this:
x = SUMMARIZECOLUMNS(
‘ColumnSource’[ColumnName],
TREATAS({“Type1”}, ‘Types’[Type]),
“Score”, average(‘Detail’[Score]),
“test”, “x”
)
Detail table has ColumnName and Score and is related to ColumnSource on the ColumnName field.
ColumnSource has a field named Type that identifies the subset of columns that I want visible. Other columns may have data in Detail, but I only want to see the subset.
In the absence of the “test”, “x” line, Type filters ColumnSource and only the selected columns are displayed along with the Score values. When that line is added, “x” has results for all rows of ColumnSource, not just the filtered ones.
Any ideas on how to solve this? Appreciate any help.
John
Hi @jpr. Would it be possible for you to provide a PBIX, small sample dataset, and mock-up of your expected outcome? This would help in visualizing your issue.
After posting, I realized you must wonder why I would display a field that doesn’t depend on column context in a column. I’m actually using a custom visual that needs both pivoted and unpivoted data. My visual grabs the first value and ignores the rest.
If there’s no way to solve this issue, I’ll go with merging in a pivoted version of my unpivoted data. Downside of that is that the matrix displays columns in alphabetical order regardless of sort values and I need the unpivoted values in the first couple of columns.
Thanks, @Greg. If you’re asking for the desired result as I posted it in the .pbix, an example would be this result for selecting “Type1” in the slicer.
2
8
Score
Measure
Score
Measure
13
3
256
3
If you’re asking what the larger issue is, I need a way to get a matrix to display:
Measure1
Measure2
PivotCol1
PivotCol2
PivotCol3
13
3
256
3
31
I’m doing this with a custom visual, so I can render anything if I can get the data. AFAIK, I can only get the measure data for every column (all fields are populated for all columns). This would be OK except for the dynamic column issue.
Sorry, @AntrikshSharma. PivotCols are columns created by the matrix by pivoting data. So in this case, the “column” well in the matrix is set to a table/field that contains rows for PivotCol1, PivotCol2, and PivotCol3. Measures are just fields or calculations that I need to display in the first couple of columns.
PBI can’t do that (mix fixed columns with pivoted columns) as all value fields are populated for all columns.
One workaround would be to unpivot the data and append it to the data source (it will then get pivoted with everything else), but the matrix orders pivoted columns by their name, not by the order they exist in the table, so I can’t get the display I need that way.
Thanks for posting your question @jpr . To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.
Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.
Including all of the above will likely enable a quick solution to your question.
I’m still not clear on what you’re looking for, but I think I’ve made some progress anyway. If you use an IF statement in your measure to return BLANK when [Score] is blank, then you get the first 4 columns of the matrix your looking for.
@Greg,
I greatly appreciate the effort. That solution would work if score always had a value, but sometimes it doesn’t (e.g. columns 5, 6, and 11-15 in the pbix). So in the pbix, if you select “type 1”, you will see colums 2 and 8, but not 5 or 15 (because they don’t have scores).
What you sent is helpful though and giving me another path to think more about it. What I really need is a way to see if the field is “there” even if it’s null. I may be able to do something like substitute a value for null when the value doesn’t exist in detail vs. the case where the null is because the column isn’t in the filter.
Sorry re pivotcol3, I just meant to have random values in there, but I copied/pasted from the one above.
Hi @jpr, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!