Forum Discussion
Countifs Function
I was able to get it. The table i created was from the following formula, then i just linked it to my existing table and created a slicer on the QuoteID and selected all except blank. Thank you for your help!
=DISTINCT(FILTER(table,table[CompanyID]=1))
Try to use calculated column instead of measure, it will allow you to use all possible filters in visuals:
x =
CALCULATE(
COUNTX('tbl', DISTINCTCOUNT(tbl[QuoteID])), 'tbl'[CompanyID] =1
)
Thank you for your help, but unfortunatly that was that same result as the first reply on this thread. it has a 1 where the company is 1 but not when the quote contains the company 1 by the company is not 1.
If the end goal is to simply get a count of how many items have a CustomerID of 1, you could do this:
1. Create a table of unique QuoteID's
2. Relate this to your other table, 1 -> *
3. Create the following columns in your new QuoteID table with only unique QuoteID's. In my formulas, quotes is the original table you presented and quotes2 is the one with only unique QuoteID's
Column = CALCULATE(COUNT(quotes[CompanyID]),RELATEDTABLE(quotes)) Column 2 = COUNTX(FILTER(RELATEDTABLE(quotes),[CompanyID]=1),[CompanyID]) Column 3 = [Column]*[Column 2]
You can now simply SUM [Column 3] to get your number.
Again, without the real reason around what you are trying to accomplish, not sure if this solution will work for you.
I think you are on the right track of what i would like to do, and you have possibly given me another solution, but can you help me with one thing. Can i create a table of unique QuoteID's where CompanyID = 1. If i can do this, then i can link this to my other table and i wouldn't need any formulas, i would just be creating an inner join of sorts, and all the QuoteID's that do not contain Company 1 then would be removed.
I don't know what is needed to create a table, it looks like i can use a formula though.
