Presenting a table with 0 or 1

[Natial]

Hi all,

I'm struggling with something here...

I have a list of users and the items they completed:

User 1	item 1
User 1	item 2
User 2	item 1
User 3	item 1
User 3	item 3
User 4	item 1
User 4	item 2

I filtered in a page the must-have items (let's say only items 1 and 2), and as values I counted the user ID, and I created a Matix that presents the user in rows and the must-have items in columns and show who completed what in the following way:

	item 1	item 2
User 1	1	1
User 2	1
User 3	1
User 4	1	1

The problem is when I want to show how many items were completed out of the mandatory items each filter changes the calculation of the required items. For example, I want to show that user 3 completed 1 out of 2 items, but when I'm selecting this user, item 2 disappears. I thought about padding the table with 0 where there's an empty value, but I didn't manage to do it. 

I also tried to create a DAX formula with ALL items:

All_completed_items = CALCULATE(

DISTINCTCOUNT('Items'[title]))

All_mandatory_items = CALCULATE([All_completed_items],ALL('Items'[User ID]))

But again, when I'm filtering a specific user, everything changes. 

Sorry for the long description. I'm trying to be as clear as I can. I hope it makes sense. 

Thanks for your help! 

[Fowmy · Accepted solution]

@Natial 

You need to have a Users and Items as seperate dimension tables then create a relationship then add +0

Create User Table as follows, do the same for Items

Model

Result

[Fowmy · Accepted solution]

@Natial 

You need to have a Users and Items as seperate dimension tables then create a relationship then add +0

Create User Table as follows, do the same for Items

Model

Result

[Natial]

Thank you so much @Fowmy it worked like magic 🙂 Can you please help me with one more thing? How do you then sum up all items? For example, I would like to add a Gauge that will show in max value the total items (3 in this case) and present as the value that User 3 completed 2 out of 3 items?

[Fowmy]

@Natial 

Create the following two measures and drop them in the gauge visual 

Completed By User = 
CALCULATE(
    COUNT(Table3[Item]),
    REMOVEFILTERS(Items)
)

Total Items = 
CALCULATE(
    COUNTROWS(Items),
    REMOVEFILTERS(Items)
)

[Natial]

@Fowmy thanks mate but the max number should be only the mandatory items that I filtered on the page. 
When I'm following your measures I get all the items that exist in the dataset instead of the mandatory ones. 

[Fowmy]

@Natial 

You explamation says "present as the value that User 3 completed 2 out of 3 items?" for the selected User which is User 3.
Please clarify

[Natial]

Sorry @Fowmy , my comment was wrong. I meant to say that according to my example, I would like to show that user 3 completed only 1 item out of the 2 mandatory items (when there are 3 items in total). 
I will clarify. I have a lot of items in this dataset, but I filtered in the page only the mandatory ones (using the page filter). Now, I would like to show them how many mandatory items they completed out of the total mandatory items (not all items).  

[Fowmy]

@Natial 

In that case, change your Total Item Measure as follows

Total Items = 
COUNTROWS(
    VALUES( Items[Item] )
)

Now you can use the previously created COMPLETED measure for Value section and the Total Item measure for the Maximum value section of the visual

[Natial]

@Fowmy thanks.
Can you show me the completed measure, please? 
I used the following formula for measuring completed items:

COALESCE ( COUNTROWS ( 'Items' ), 0 ) 

Your measures:

Gave me all items that users completed regardless if it's mandatory or not. 

Completed By User = 
CALCULATE(
    COUNT(Table3[Item]),
    REMOVEFILTERS(Items)
)

I must be doing something wrong... 

[Fowmy]

@Natial 

Completed = 
CALCULATE(
    COUNTROWS('Table3')
)+0

[Natial]

Thank you so much for your help and patience @Fowmy  🙂 the completed measure works well now. One thing that I think that's making all the mess is my "items" table. Instead of taking only the mandatory items, it has all the items, so my max number is much higher than it should be. I wonder if the items table that you created includes only the 2 mandatory items. In that case, how did you do that?

[Natial · Accepted solution]

Hi @Fowmy , first, thanks again for your help I just wanted to let you know that together with your help, I managed to solve it. The function that I was missing was:

ALLEXCEPT() to get the total number of mandatory items I needed without changing the total number when selecting one of the users. So the final formula of total items should be:

Total Items = 
CALCULATE(
    DISTINCTCOUNT(Table3[Item]),
    ALLEXCEPT(Table3[User ID])
)

Thanks again for your help!

[tamerj1]

Hi @Natial 
You may try 

COALESCE ( COUNTROWS ( 'Items' ), 0 )

[Natial]

Same result 😞