How to return string values in the Matrix visual values, I tried using coalesce function but it shows only the counts or distinct counts. I cannot modify the source data that is dataset.
I need to convert this table:
Issue ID Attribute value
1 Priority high
1 cat a
1 xxx bb
2 Priority mediam
2 cat b
2 xxx cc
to Pivot chart like this:
Issue Id Priority cat xxx
1 high a bb
2 medium b cc
Hello @rama_kumar , welcome to the QuickSight community!
My first thought is to try making new calculated fields to manage the pivot. You could try the following:
Priority = ifelse({attribute} = 'Priority", {value}, NULL)
Do the same for cat and xxx, and you should be able to leave issue id. Then add your new calcs and issue id to your table.
Let me know if that works.
Hi @duncan ,
Thanks for your response. I tried the calculated field as suggested. It gives additional null values and not pivoting as expected. Please refer the screenshot.
Is it possible to fix this with firstValue function?
any update on this please?
Hey @rama_kumar
If you have empty strings or null values in rows of your dataset then it will return them.
To exclude rows with null values or empty strings you could create filters on your visual from your calculated fields where the conditions is set to “does not equal” and leave the value blank, then at the bottom of the filter manage null values by switching the drop down to “exclude nulls”.
Let me know if this works.
Hi @duncan ,
Thanks, no luck! This works only if there is one calculated filed the dashboards we are working on have multiple calculated fields. Is it a native function in quicksight that does not allow returning or aggregating text values in the matrix visual as values?? It converts to first value function by default in Power BI once the text value column is added in the Values field. Similar workaround in quicksight can be helpful.
Hello @rama_kumar
In QuickSight, tables and pivot tables default to aggregating text values as count or count distinct.
I can mark this as a feature request for the QuickSight team.
