Replacing blanks with 0 issue and data modeling issue

[Mourt]

Hi everyone,

Unfortunatlry I wont be able to upload a sample pbix, but I will try to explain

The first table that I have is called Location Table and it has 3 locations 

Location_ID	Location_Title
1	Location 1
2	Location 2
3	Location 3

the Second table is the users table, each user belongs to atleast 1 location and could also be linked with multiple locations.

User_ID	Location_ID	User_name
111	1	AAA
111	2	AAA
112	1	BBB
113	2	CCC
114	1	DDD
114	3	DDD
115	2	EEE
116	1	FFF
117	1	GGG
118	2	HHH
119	2	III
119	3	III
120	1	JJJ
120	2	JJJ
120	3	JJJ

The third table is called the audits table, each user could be audited in his location.

Audit_ID	User_ID	Location_ID
1234	111	1
1235	112	1
1236	114	3
1237	115	2
1238	117	1
1240	119	3
1241	120	2
1242	111	1
1243	112	1
1246	117	1
1248	119	3
1249	120	2
1250	112	1
1251	111	1
1252	112	1
1253	119	3
1254	120	3
1255	114	3
1256	111	1
1257	114	3
1258	120	3
1259	119	3
1260	120	2

My First issue:
I created a mtrix where it shows the 3 locations with a drill down to users that are link with the location. I used show items with no data and all users are showing but the ones with no audits are not showing, I tried to add +0 to the measure or If(ISBLANK but when I do that all users show under the Location whether they are linked to it or not.

My Second issue:
The users that are linked to multiple locations are showing the same number of audits accross all locations, eventhough they could have been audited in only one of their locations, Im trying to figure out a way to show the number of audits based on the User and the location together not only the user.


Data Model:


Expected Result

ignore the last column

Thank you and appreciate your support

[Mourt · Accepted solution]

Combine the Location ID and User ID on your Users table by creating a column using concat function or (&) operator. Do the same in Audits table. 

[Mourt]

Up?

[TomMartens]

Hey @Mourt ,

please take the time and prepare some sample data, that still reflects your data model. Paste the data into your post and describe the expected result based on the sample data you provide.

Regards,

Tom

[Mourt]

Hi @TomMartens , 

thank you for your reply, I have updated the post and added the data samples