Forum Discussion
Ignore grouping if value is found
Did this in PQ and DAX, but I think PQ was a little easier and probably performs better, but a preference.
So for PQ:
- Load data in PQ
- Duplicate the query (I named as Grouped Table)
- Grouped the Rows by ID and Date
- Add a column to the table to look for "X" in any of the rows of the grouped tables
- if so, returns true, if not, returns false
- remove duplicates
- Go back to original query ( what i called the Final Table)
- Merge the final table with the Grouped table
- Expand the "Has x?" column
You get a final table that looks like:
Then a simple distinct count measure:
Dist Count =
CALCULATE(
DISTINCTCOUNT('Final Table'[id ]), 'Final Table'[Has x?] = FALSE)and you get this:
and here's the DAX way ( didnt fully troubleshoot this one, so there may be an issue or two)
Here's the pbix:
Helper IIThanks for the reply Nick_M, will try these methods on my fact table and let you know if this works, but will it be good if I create a group table on a big fact table?
the go to answer is , " it depends". The size of the table really isn't the issue, it's the cardinality of it. And if you are getting this data from a relational database you can use query folding to push the heavy lifting to the database.
If you do this in DAX you will probably need to use summarize or something simliar, which produces a table in memory. Tables in memory do not take advantage of Vertipaq so performnace might suffer, which can make it a slow experience for the end user.
Physical tables are only updated at refresh time, so given the choice (along with query folding if possible) I'd always go the way of PQ when possible. Give it shot on your side and see what happens. :smileyhappy:
- joepath
Forget to mention one point, we have multiple page level filters. after applying those filters also grouping should work,
what I mean to say, once we apply filters than on the filter data set grouping logic should work. In that case also PQ logic work? or should we go by Dax approach because that will be dynamic grouping?
v-lili6-msftCommunity Support
hi, joepath
First, you should know that calculated column and calculate table can't be affected by any slicer.
Notice:
1. Calculation column/table not support dynamic changed based on filter or slicer.
2. Measure can be affected by filter/slicer, so you can use it to get dynamic summary result.
here is reference:
https://community.powerbi.com/t5/Desktop/Different-between-calculated-column-and-measure-Using-SUM/t...
https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/Second, you may try to use this formula to create a measure:
Measure = var _table= SUMMARIZE(Table1,Table1[id],Table1[date],"judge",IF(CALCULATE(COUNTA(Table1[value]))=CALCULATE(COUNTA(Table1[value]),FILTER(Table1,Table1[value]="y")),1,2)) return COUNTROWS(FILTER(_table,[judge]=1))
Result:
Best Regards,
Lin