topic Re: Creating Categories based on counts in DAX Commands and Tips

Creating Categories based on counts

Anonymous — Tue, 11 May 2021 19:30:42 GMT

I have a list of people that receive 1 letter monthly. People are added to the list, so now, if I look back - I want to understand and craft the following based on people and count of letters received in past 32 months.

Here is a mock list:

people	total # of letters
pp1	32
pp2	32
pp3	16
pp4	16
pp5	28
pp6	27
pp7	28
pp8	27
pp9	16
pp10	12
pp11	12
pp12	12
pp13	4
pp14	4
pp15	4
pp16	4

The goal is to create a bar chart and each total #, report the number of people that received that many letters in past 32 months, then on top of that, add how many replied to the letter that was sent (%)

Not sure how to craft this in a formula.

Re: Creating Categories based on counts

HashamNiaz — Tue, 11 May 2021 23:23:27 GMT

Hi !

Please share how you are currently tracking No. of Letters delivered vs. Reply recieved in your Data model.

Regards,

Re: Creating Categories based on counts

Anonymous — Wed, 12 May 2021 00:02:14 GMT

the table has each user individual, the date, and Yes or No if sent, and yes or no if responses, then yes or no if account updated. After importing the table into PBI, I then just pulled up a matrix, and have the people in column, then in values - count of Sent (filtered on sent value as yes), so shows me the count of sent based on the filter of the date as well in the filter section.

Re: Creating Categories based on counts

HashamNiaz — Wed, 12 May 2021 00:15:07 GMT

Hi !

You can get started by using following 2 measures;

Letter Sent = CALCULATE( COUNT(YourTable[People]), FILTER(YourTable, YourTable[Sent] = "Yes")) Letter Recieved = CALCULATE( COUNT(YourTable[People]), FILTER(YourTable, YourTable[Responses] = "Yes"))

You can replace the YourTable with table name, we consider [People] as column name in your model, this is the column representing each individual, or you can use the Date column as well. We consider Sent & Reponses are 2 columns in your model to pick Sent = "Yes" to get No. of Letter sent count and Repsonses = "Yes" to get No. Of Letter recieved count.

Regards,

Hasham

Re: Creating Categories based on counts

Anonymous — Wed, 12 May 2021 01:13:30 GMT

Is there a way to filter the calculation based on a subset - example, i have 2 tables, the data that holds the people and response information, and the more people detailed information - both connected by peopleEmail. I further have a subset, a list of people names, details, and emails - i want to calculate just only for those, so as long as they match are in the response information table. Hope that makes sense. I keep getting filter expression error due to true/false statement. My thoughts are this - (it failed - but a start i guess)

Letters Sent Group1 = CALCULATE( COUNT(ResponseTble[email],

FILTER(ResponseTble, 'ResponseTble'[Sent] = "Yes"),

FILTER(ResponseTble, 'ResponseTble'[email] in

VALUES('Group1'[email])

)

Re: Creating Categories based on counts

HashamNiaz — Wed, 12 May 2021 01:42:30 GMT

Hi !

I would rather prefer to solve this problem through Data Modeling. You have ResponseTble and Group1 table, both are connected through [email] column. You can put a slicer on your report from Group1 table to filter selected Groups. This will apply filter based on selection and only those emails will be checked in ResponseTable where the Group1 [email] criteria is matched. And then second filter will apply through DAX to check [Sent] = "Yes"

Hope this will help you get resolved the issue.

Regards,

Hasham

Re: Creating Categories based on counts

Anonymous — Wed, 12 May 2021 19:22:24 GMT

That worked!!! Thanks!!