Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello, I am pretty new to PowerBI, and I am trying to write a DAX expression for a column that would use Excel's version of countifs. Below is my data, the final column would be the output. The formula i am using in excel to acheive column 3 is COUNTIFS($A$2:$A$13,A2,$B$2:$B$13,1). Can someone help me build this column in PowerBI with DAX? Basically i am trying to create a filter that gives me only the quotes with companyid 1 included. Thanks in advance.
QuoteID CompanyID CalculatedColumn
60525 1 1
60525 1245 1
60525 1375 1
60525 1475 1
223877 1245 0
223877 1375 0
223877 1475 0
223877 1758 0
223877 1968 0
458623 1968 1
458623 1 1
458623 1275 1
458623 1258 1
458623 1678 1
Solved! Go to Solution.
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))
Hi @amotto11,
Try this calculated column formula
=CALCULATE(COUNTROWS(Data),FILTER(Data,Data[QuoteID]=EARLIER(Data[QuoteID])),Data[CompanyID]=1)
Hope this helps.
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.
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))
Awesome! Happy to help you get to a solution!
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
)
Measure = CALCULATE(COUNT([QuoteID]),[CompanyID]=1)
smoupre, 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.
Try 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))
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.
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.
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.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
104 | |
95 | |
80 | |
67 | |
62 |
User | Count |
---|---|
147 | |
109 | |
107 | |
85 | |
63 |