cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Red_prov
New Member

Matrix Rows and Columns disappearing when count ID removed & unwanted grouping happening

Hello!

 

I have a matrix that lists open tasks broken up into categories. So the user is able to expand these different pools to see what kind of tasks are still open. 

 

In order to hide the data unless the matrix was expanded/drilled all the way down i used this formula

NoPrevFirstName = IF (ISINSCOPE(Query1[RequestId]),SELECTEDVALUE(Query1[FirstName]), BLANK())
Red_prov_0-1663783934774.png

 

However now the entire matrix disappears unless i have a count ID to display totals. 
 Red_prov_1-1663783957997.png

 

Also if theres more than 1 record for a person it displays no information in the field. its blank. 
 So if Jane Doe has 1 task open and she needs access to a certain thing it will display it. however if Jane has 4 tasks open. it will just show a count of 4, but not display the tasks individually. 
Red_prov_4-1663784123486.png

 

this is working in SSRS. users tasks are labeled out individually but it appears some grouping is going on in BI.
 
 
 
5 REPLIES 5
Red_prov
New Member

I believe i found why the grouping is happening. my last row in my BI report is the id related to the individual request that can have multiple tasks tied to it. i am using that to display the record information. so in instances where multiple tasks are tied to that same ID that fall into this section its not displaying them individually. just grouping based on the id. 

Red_prov_0-1663791664764.png

 

However in ssrs its just displaying the requestID in the record/row information and not using is as a grouping. 

Red_prov_1-1663791761422.png

however excluding this ID i dont get the same visual 

Red_prov_2-1663792169384.png

however when movingthe requestID from row to value section in the powerBi visual it doesnt display all records that fall into the previous category like ssrs above. it just tosses them in an aggregate so sum/count whatever for the column value 

Hi, @Red_prov 

I am still confusing about your requirement.

Please share a sample pbix, so that we can post detailed solution.

Best Regards,
Community Support Team _ Eason

Hello, i am attempting to upload the test pbix/xlsx however i am gettig a red banner saying the file types arent support. 

 

I can post screenshots of both 

Red_prov_1-1663864145364.png

 

Above is the test data. i have 4 records, 3 related to 1 reqID, while the others standalone. 

When expanding the matrix i want to see all records. however i have not found a way to setup the matrix yet to allow this.

 

Red_prov_2-1663864210498.png

 

 

HI, @Red_prov 

You may need to add the 'ID' field to the "Rows" of the matrix not the "Values".

veasonfmsft_0-1663902544734.png

Best Regards,
Community Support Team _ Eason

 

 

Hello!

 

I will work on getting a sample file working to display my concern later today. 

 

But just to try to clarify in the mean time. i have a matrix with the rows listed above. You can expand the matrix to the requestID level. However in doing this. when expanding a category if the level your expanding would have multiple entries for the request id. instead of listing them out multiple times it groups them into the same count. when expanding the matrix to the last detail level. if i have 4 records i want them all listed individually. Howeve it appears that the matrix is grouping them together instead of listing them out. Referencing the first screenshots i attached. when fully expanded if the count is greater than 1 it has multiple task strings. so its empty due grouping. instead of that grouping i want them to be listed individually. But if i dont use the requestID as the final row level. the matrix doesn't display the data in the same format, and it also aggregates the rows displaying counts. when fully expanding application category as the final drill through i want to show all records listed. not group some so they arent visible. 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors