topic Re: Presenting a table with 0 or 1 in DAX Commands and Tips

Presenting a table with 0 or 1

Natial — Tue, 16 Aug 2022 14:16:54 GMT

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!

Re: Presenting a table with 0 or 1

tamerj1 — Tue, 16 Aug 2022 14:28:16 GMT

Hi Natial
You may try

COALESCE ( COUNTROWS ( 'Items' ), 0 )

Re: Presenting a table with 0 or 1

Natial — Tue, 16 Aug 2022 14:35:22 GMT

Same result 😞

Re: Presenting a table with 0 or 1

Fowmy — Tue, 16 Aug 2022 14:45:16 GMT

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

Re: Presenting a table with 0 or 1

Natial — Tue, 16 Aug 2022 15:18:37 GMT

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?

Re: Presenting a table with 0 or 1

Fowmy — Tue, 16 Aug 2022 18:40:34 GMT

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) )

Re: Presenting a table with 0 or 1

Natial — Wed, 17 Aug 2022 07:31:44 GMT

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.

Re: Presenting a table with 0 or 1

Fowmy — Wed, 17 Aug 2022 09:45:38 GMT

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

Re: Presenting a table with 0 or 1

Natial — Wed, 17 Aug 2022 10:13:28 GMT

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).

Re: Presenting a table with 0 or 1

Fowmy — Wed, 17 Aug 2022 11:21:24 GMT

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

Re: Presenting a table with 0 or 1

Natial — Wed, 17 Aug 2022 12:56:14 GMT

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...

Re: Presenting a table with 0 or 1

Fowmy — Wed, 17 Aug 2022 13:19:58 GMT

Natial

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

Re: Presenting a table with 0 or 1

Natial — Wed, 17 Aug 2022 13:48:13 GMT

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?

Re: Presenting a table with 0 or 1

Natial — Thu, 18 Aug 2022 09:48:35 GMT

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!