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.
Hi All,
I am trying to run the Countif command in Power BI. I am using the following code for the same.
However, as I have mentioned in the picture, I want the query to give me an answer in such a way that if first row is checked and Ash has worked for Forbes once, then it should consider it as 1. But if again Ash's name pops up with same company i.e. Forbes, then it should be 2 as it is coming for the 2nd time around for the same Company.
Can this be done? Kindly please help.
I am also attaching the Excel code for the same so that you can get a better idea.
Thank you,
Regards,
Saumitra Upadhyaya
Solved! Go to Solution.
Hi @saumitra ,
When "Ash Forbes" appears multiple times, two rows data are filtered out because the Date column in table is the same. You should add a column that can determine the order in which "Ash Fobes" appears. So I added an "index" column to the table, and then create the following calculated column.
Countif =
COUNTAX(
FILTER(
'Sheet1',
'Sheet1'[Name] = EARLIER('Sheet1'[Name])
&& 'Sheet1'[Company] = EARLIER('Sheet1'[Company])
&& 'Sheet1'[index] <= EARLIER('Sheet1'[index])
),
'Sheet1'[Name]
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @v-kkf-msft ,
I wanted to know how to get the above outcome for different dates separately? I have linked a folder to Power BI, so I keep on adding data of different dates everyday. I would like the outcome to be as above for separate dates. Is that possible?
Thanks,
Regards,
Saumitra Upadhyaya
Hi @saumitra ,
When "Ash Forbes" appears multiple times, two rows data are filtered out because the Date column in table is the same. You should add a column that can determine the order in which "Ash Fobes" appears. So I added an "index" column to the table, and then create the following calculated column.
Countif =
COUNTAX(
FILTER(
'Sheet1',
'Sheet1'[Name] = EARLIER('Sheet1'[Name])
&& 'Sheet1'[Company] = EARLIER('Sheet1'[Company])
&& 'Sheet1'[index] <= EARLIER('Sheet1'[index])
),
'Sheet1'[Name]
)
If the problem is still not resolved, please provide detailed error information or the expected result you expect. Let me know immediately, looking forward to your reply.
Best Regards,
Winniz
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@v-kkf-msft Hey, thank you very much. I am getting the desired outcome now. However, just one query. In my example, the dates are same but suppose in future the dates are different, then what can be done?
Thanks again for solving the above problem. 🙂
Hi @saumitra ,
If the same Name and Company appear on different dates, then the DAX you write in the first picture is right.
Best Regards,
Winniz
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 |
---|---|
115 | |
99 | |
86 | |
70 | |
62 |
User | Count |
---|---|
151 | |
120 | |
103 | |
87 | |
68 |