topic Re: How to count 2 items in a single row with multiple items listed in DAX Commands and Tips

How to count 2 items in a single row with multiple items listed

scyllanbay — Thu, 27 Aug 2020 13:21:12 GMT

I have been spinnig my wheels trying to figure out the correct DAX formula to provide me with a total count of certain words in a single column. Example: I have a column for status with items such as "awarded", "not awarded", "pending award", "Lost", etc. etc. I am looking for the correct way to tell the measure to count all of the awarded and not awarded in that same column. Is that possible?

Re: How to count 2 items in a single row with multiple items listed

camargos88 — Thu, 27 Aug 2020 13:37:39 GMT

Hi scyllanbay ,

Try this measure:

_Count = CALCULATE(COUNTA('Table'[Column1]), FILTER('Table', 'Table'[Column1] IN {"awarded", "not awarded"}))

If you need a distinct count, just use:

_Count = CALCULATE(DISTINCTCOUNT('Table'[Column1]), FILTER('Table', 'Table'[Column1] IN {"awarded", "not awarded"}))

Re: How to count 2 items in a single row with multiple items listed

scyllanbay — Thu, 27 Aug 2020 13:43:28 GMT

I tried both of these and I am getting an error that says, "too many arguments were passed to the distinctcount function. the maximum argument count for the function is 1."

Re: How to count 2 items in a single row with multiple items listed

camargos88 — Thu, 27 Aug 2020 13:51:30 GMT

See this example:

Re: How to count 2 items in a single row with multiple items listed

scyllanbay — Thu, 27 Aug 2020 13:56:00 GMT

I got the formula to work and provide a 2... however, I need it to count all of the awarded and all of the not awarded and pull back that total number which may be 53. Sorry, for the back and forth, I'm taking classes right now and learning but it's a tricky thing to learn.

Re: How to count 2 items in a single row with multiple items listed

scyllanbay — Thu, 27 Aug 2020 13:59:08 GMT

I changed it to counta and that worked. Is there now a way to add another column in this formula to pull out another item such as "hard bid". So, in the end it would be giving me the total count of all awarded and not awarded in column A and the total hard bid in column B. To get a true number of hard bids that were awarded and not awarded?

Re: How to count 2 items in a single row with multiple items listed

camargos88 — Thu, 27 Aug 2020 14:01:07 GMT

scyllanbay ,

You can use input the values here:

_Count = CALCULATE(COUNTA('Table'[Column1]), FILTER('Table', 'Table'[Column1] IN {"awarded", "not awarded", "OTHERS"}

and Column2 = "ABC"))

If you have a sample dataset I can help you with a pbix.

Re: How to count 2 items in a single row with multiple items listed

scyllanbay — Thu, 27 Aug 2020 14:08:54 GMT

Is there a way to attached a csv file on here?

Re: How to count 2 items in a single row with multiple items listed

camargos88 — Thu, 27 Aug 2020 14:09:53 GMT

scyllanbay ,

You can use google drive / one drive for it, and share the link here.

Re: How to count 2 items in a single row with multiple items listed

scyllanbay — Thu, 27 Aug 2020 14:14:10 GMT

https://drive.google.com/file/d/1Z9SkhkZmm_635clOEL1uLFKKNo8890Sg/view?usp=sharing

I think that will work. The first tab has my data and the second tab is what I am trying to create in Power BI. Below is what I have so far, except the Total Hard Bid column is the one that I'm currently working on and it's 100% correct yet. I have a lot of learning left to do and multiple classes on udemy already purchased 🙂

Re: How to count 2 items in a single row with multiple items listed

camargos88 — Thu, 27 Aug 2020 14:21:06 GMT

scyllanbay ,

I only see a csv file with Hard Bird and Negotiated, I believe there are 2 datasets here, right ?

Sorry I didn't get the connection with the first question. Can you explain it ?

Re: How to count 2 items in a single row with multiple items listed

scyllanbay — Thu, 27 Aug 2020 14:28:14 GMT

https://drive.google.com/file/d/1Zm3hzluvTFGdv50wv4yvB6bZwMNnK8V8/view?usp=sharing

The one that you are seeing the the graph that I'm trying to produce in Power BI to look like the below. The attached is the actual data set.

Re: How to count 2 items in a single row with multiple items listed

camargos88 — Thu, 27 Aug 2020 15:07:32 GMT

scyllanbay ,

See the file attached.

I didn't get how you calculated the Prediction column.

Re: How to count 2 items in a single row with multiple items listed

scyllanbay — Thu, 27 Aug 2020 15:23:46 GMT

Honestly, I'm speechless. You have no idea how much I appreciate this and didn't expect you to pull all of that together, but Thank you!

He calculated the prdiction column by multiplying the percentage by pending.

One more question, there is an "R" showing before all of the $ values. Do you know why that is?

Re: How to count 2 items in a single row with multiple items listed

camargos88 — Thu, 27 Aug 2020 15:27:44 GMT

scyllanbay ,

That's because I've formatted this column as currency, I'm in Brazil that's why you are seeing this "R$"....

You can change it clicking here:

Re: How to count 2 items in a single row with multiple items listed

scyllanbay — Thu, 27 Aug 2020 16:21:05 GMT

Thank you sooo much for helping me with this. You have saved me so many hours of beating my head against my desk trying to figure this out.