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.
Please i need help i counting all the value in the cell fileds as unique count without counting the blank cell, and have the total unique count sum up. As shown in the Pictures, please what measures can i use?
Solved! Go to Solution.
Hi @nosirui54
If you need a column use this code:
Column =
CALCULATE (
COUNT ( 'Table'[Months] ) + COUNT ( 'Table'[Year] )
+ COUNT ( 'Table'[Number] )
+ COUNT ( 'Table'[FIPS] )
+ COUNTA ( 'Table'[Postal_Code] )
+ COUNT ( 'Table'[State] )
+ COUNT ( 'Table'[County] ),
FILTER ( 'Table', 'Table'[Months] = EARLIER ( 'Table'[Months] ) )
)
Output:
If you need a measure:
Measure =
CALCULATE (
COUNT ( 'Table'[Months] ) + COUNT ( 'Table'[Year] )
+ COUNT ( 'Table'[Number] )
+ COUNT ( 'Table'[FIPS] )
+ COUNTA ( 'Table'[Postal_Code] )
+ COUNT ( 'Table'[State] )
+ COUNT ( 'Table'[County] ),
FILTER ( 'Table', 'Table'[Months] = max ( 'Table'[Months] ) )
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Hi @nosirui54
Can you post sample data as text and expected output?
Not enough information to go on;
please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables
Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/
Thank you for your response. What am trying to achieve is this:
1. I want each Cells with Values to count as one and then show counts total in the Expected_Output_Counts Column
2. If the cell is blank it shouldnt count blank cell as value, because is blank
Months | Year | Number | FIPS | Postal_Code | State | County | Expected_Output_Counts |
January | 2001 | 1 | 1000 | 36003 | Alabama | Autauga | 7 |
February | 2002 | 2 | 1001 | 36003 | Alabama | Autauga | 7 |
March | 2003 | 3 | 1003 | 31034 | Alabama | Baldwin | 7 |
April | 2004 | 4 | 1005 | 26238 | Alabama | Barbour | 7 |
May | 2005 | 5 | 1007 | Texas | Valley | 6 | |
June | 2006 | 6 | 1009 | 4 | |||
July | 2007 | 7 | 1011 | 4 | |||
August | 2007 | 8 | 1011 | Texas | 5 |
Hi @nosirui54
If you need a column use this code:
Column =
CALCULATE (
COUNT ( 'Table'[Months] ) + COUNT ( 'Table'[Year] )
+ COUNT ( 'Table'[Number] )
+ COUNT ( 'Table'[FIPS] )
+ COUNTA ( 'Table'[Postal_Code] )
+ COUNT ( 'Table'[State] )
+ COUNT ( 'Table'[County] ),
FILTER ( 'Table', 'Table'[Months] = EARLIER ( 'Table'[Months] ) )
)
Output:
If you need a measure:
Measure =
CALCULATE (
COUNT ( 'Table'[Months] ) + COUNT ( 'Table'[Year] )
+ COUNT ( 'Table'[Number] )
+ COUNT ( 'Table'[FIPS] )
+ COUNTA ( 'Table'[Postal_Code] )
+ COUNT ( 'Table'[State] )
+ COUNT ( 'Table'[County] ),
FILTER ( 'Table', 'Table'[Months] = max ( 'Table'[Months] ) )
)
Output:
If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Its Work. Thank you SO much for the Solutions.
Please is there a way to avoid the Filters? because some of the Columns i will be filtreing with does not have Value.
No Worries.
Can you please tell me more or share a sample re that Filter issue?
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/
Thank you for your time. What am trying to say is that what if the colomn does not have Calendar and am trying to achieve the same result as shown below: Thank you
Number | FIPS | Postal_Code | State | County | Expected_Output_Counts |
1 | 1000 | 36003 | Alabama | Autauga | 5 |
2 | 1001 | 36003 | Alabama | Autauga | 5 |
3 | 31034 | Alabama | Baldwin | 4 | |
1005 | 26238 | Alabama | Barbour | 4 | |
5 | 1007 | Texas | Valley | 4 | |
6 | Indinana | Marion | 3 | ||
7 | 1011 | 2 | |||
1011 | Texas | 2 |
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 |
---|---|
106 | |
94 | |
77 | |
65 | |
53 |
User | Count |
---|---|
145 | |
105 | |
104 | |
90 | |
63 |