Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
Mourt
Helper I
Helper I

Filtering is not giving giving the correct output

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

Groupannonymous_submission_idRole_code
A11
A11
A21
B31
B41
C51
C51
C61
C71
D81
D91
D91
D101
D111
A122
A122
A132
B142
B152
C162
C162
C172
C182
D192
D202
D202
D212
D212
A223
B233
C243
D253


Table 2 

GroupUser ID
A113
A242
A114
B122
B274
B216
B126
B256
C267
C279
D297
D133
D229
E842



Mourt_0-1641742685257.png

 

 

5 REPLIES 5
ryan_mayu
Super User
Super User

@Mourt 

 

pls see the attachment below

group.PNG





Did I answer your question? Mark my post as a solution!

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

@Mourt 

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.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




amitchandak
Super User
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

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.