I want to write a DAX which count the rows based on the grouping columns 'id' and 'date', but at the same time want to ignore the grouping count if the value contains x. below is the expected result. id date value a 19-Jan x a 19-Jan y c 20-Jan y d 20-Jan x e 21-Jan y e 21-Jan y Result c 20-Jan y e 21-Jan y countrow =2

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 PQDuplicate the query (I named as Grouped Table)Grouped the Rows by ID and DateAdd a column to the table to look for "X" in any of the rows of the grouped tablesif so, returns true, if not, returns falseremove duplicatesGo back to original query ( what i called the Final Table)Merge the final table with the Grouped tableExpand the "Has x?" columnYou 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:https://1drv.ms/f/s!AoQIGRpzoxRHgbxJFlcjEL9_gHrSoA

Thanks 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:

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?

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

Ignore grouping if value is found | Microsoft Fabric Community

14 Replies

Anonymous
Not applicable
7 years ago
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:
https://1drv.ms/f/s!AoQIGRpzoxRHgbxJFlcjEL9_gHrSoA
- joepath
  Helper II
  7 years ago
  Thanks 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?
  - Anonymous
    Not applicable
    7 years ago
    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:

Forum Discussion

Ignore grouping if value is found

14 Replies

Related content

IGNORE NEGATIVE VALUES

Fabric & Azure Data User Group Program Requirements & Benefits

Average by group ignoring Sub-Group

sum function ignoring nagetive values

Community User Group Experience