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 there,
I'm trying to figure out how to calculate, distinct count and filter based on a column having a value >= 1.
How can I filter out the rows based on there not being a value in the column? Basically, if the cell IS BLANK, then it should show up in this table.
hi, @dandywong
It seems that you want to distinct count by Grouping。
You may try to use EARLIER Function in your formula
for example
Count 2 = CALCULATE(DISTINCTCOUNT(Table2[Column2]),FILTER(Table2,NOT(ISBLANK(Table2[Column2]))))
Count 3 = CALCULATE(DISTINCTCOUNT(Table2[Column2]),FILTER(Table2,Table2[Column1]=EARLIER(Table2[Column1])&&NOT(ISBLANK(Table2[Column2]))))
Result:
If not your case, please share your sample pbix and expected output for us. You can upload it to OneDrive or Dropbox and post the link here. Do mask sensitive data before uploading.
Best Regards,
Lin
Sorry, if cell IS BLANK, it should not show up in this table
You will want to use something like
FILTER(Tab, NOT(ISBLANK(Tab[blankable column])
as a parameter on CALCULATE.
Also, be aware that sometimes a value is BLANK, sometimes it is just ""
Hope this helps.
David
Thanks @ dedelman_clng, but it did something weird with the count of the column or did I not get the script right?
It looks like you're trying to create this as a calculated column instead of a measure. Try recreating as a measure and build a table visual instead of using the data preview.
Other than that we would need to see more of your model, data, etc to help troubleshoot further. 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
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 |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
129 | |
106 | |
106 | |
86 | |
72 |