Forum Discussion
Helper IICountifs 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))
Helper IIsmoupre, thank you for your response. Unfortunatly, that is only giving me 1's where the companyID is 1, not the full QuoteID if a company is 1. Basically it is just giving a 1 in the same rows that the company is a 1. I would like it to give a 1 in any row if the company 1 shows up in the QuoteID.
Impactful IndividualTry the below measure:
Measure = IF(CALCULATE(DISTINCTCOUNT(Table1[Quote ID]),Table1[Company ID]=1)=0,0,CALCULATE(DISTINCTCOUNT(Table1[Quote ID]),Table1[Company ID]=1))
- amotto11
Thank you for your response, but unfortunatly that was the same result as the previous response, but instead of Blanks it put 0's. Basically it has a 1 where there is a 1 for the company, but 0's everywhere else. I would like to have a 1 be on every row where the QuoteID contains the 1 company. So even though the company on that row isn't a 1, if the QuoteID contains the 1 company, then that row should be a 1.
- Greg_Deckler
Community Champion
I think I understand what you are going for now. I'll take a look at how to do that. One question though is why do you feel this is what you need? I'm not understanding the use case behind it. There may be different solution if I could conceptualize what you are trying to do exactly.
- amotto11
Sure, i understand that. This is just my solution to the problem i have, but if you have a better one then that would be great. First off, unfortunatly i lost my link to my data so i only have the data in the PowerBI workbook, so nothing can be done outside of PowerBI. Normally i would handle this in SQL but since my raw data has been wiped i cannot do that.
I am trying to filter my data to only look at the quotes where they contain company 1. So basically i am creating a column that will let me filter my data, thereby filtering all my ranking function so that i am only looking at quotes where they contain company 1. I really would like to delete any of the quotes that don't have company 1 included, but i don't know how to do that in Power BI. Like i said, creating a column to filter on was the only solution i could come up with, and i figured the formula would be fairly easy to write, since it is easy in excel. I hope this helps you visualize what i am trying to do. Please keep in mind that the table here is around 5mm rows and has far more columns than I provided.
