Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Ignore grouping if value is found

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 re...
Need Help
joepath's avatar
joepath
Icon for Helper II rankHelper 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's avatar
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:

  • joepath's avatar
    joepath
    Icon for Helper II rankHelper II
    7 years ago

    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-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      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

       

      • joepath's avatar
        joepath
        Icon for Helper II rankHelper II
        7 years ago

        Thanks for the into v-lili6-msft, I am new to dax.  

         

        I tried your dax its working, but when I applied the slicer then it applied the logic on the filtered data set(sorry for the above comment where I mentioned it should be applied on filtered data set). but it should work on the full data set even though we use the slicer.