Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
Hello,
Im trying to filter the output based on role slicer added to table 1
for role 1 submission rate is submission counnt / total count
for role 2 the fixed count should always be 1 and total count is always 1 and if no submissions then 0 and submission rate = submission count / 1
For role 3 ignore and show blank for submission values
I have tried the following however the total is summing up all percentages
Submission % =
IF(SUM(Table1[Role])/ total submissions = 2, Divide(Fixed Submissions,1)),"")
I just wanted to filter between the roles and see the desired output, Thank you
Table 1
Group | annonymous_submission_id | Role_code |
A | 1 | 1 |
A | 1 | 1 |
A | 2 | 1 |
B | 3 | 1 |
B | 4 | 1 |
C | 5 | 1 |
C | 5 | 1 |
C | 6 | 1 |
C | 7 | 1 |
D | 8 | 1 |
D | 9 | 1 |
D | 9 | 1 |
D | 10 | 1 |
D | 11 | 1 |
A | 12 | 2 |
A | 12 | 2 |
A | 13 | 2 |
B | 14 | 2 |
B | 15 | 2 |
C | 16 | 2 |
C | 16 | 2 |
C | 17 | 2 |
C | 18 | 2 |
D | 19 | 2 |
D | 20 | 2 |
D | 20 | 2 |
D | 21 | 2 |
D | 21 | 2 |
A | 22 | 3 |
B | 23 | 3 |
C | 24 | 3 |
D | 25 | 3 |
Table 2
Group | User ID |
A | 113 |
A | 242 |
A | 114 |
B | 122 |
B | 274 |
B | 216 |
B | 126 |
B | 256 |
C | 267 |
C | 279 |
D | 297 |
D | 133 |
D | 229 |
E | 842 |
pls see the attachment below
Proud to be a Super User!
Hi @ryan_mayu sorry for the late reply I was away for the last week..
Thank you for you reply however I was looking for a way to do this through slicers in one table not separate.. is it doable?
Thank you and appreciated
since you have duplicated values in both tables. It's better to create a dimension table,otherwise the relationship is many to many. Usually we don't recommend people to use m to m relationship.
Proud to be a Super User!
@Mourt , You can try a measure like
Switch(True(),
not(isinscope(Table[Group])) , divide([submission count] ,[total count]), //grand total
max(Table[Role_code]) =1, divide([submission count] ,[total count]),
max(Table[Role_code]) =3, blank(),
// Add others
)
Hi @amitchandak Thank you for your response
I have tried the DAX but it only works properly for role 1, Role 2, the submission rate should be (submission count / 1) and other roles still show percenatges
Any advice?.. Thanks
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Prices go up Feb. 11th.
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!
User | Count |
---|---|
140 | |
71 | |
64 | |
52 | |
50 |
User | Count |
---|---|
209 | |
92 | |
64 | |
59 | |
56 |