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

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.

     

    • v-lili6-msft's avatar
      v-lili6-msft
      Icon for Community Support rankCommunity Support
      7 years ago

      hi, joepath 

      Could you share your sample pbix file or some more data sample and the formula and expected output. You can upload it to OneDrive and post the link here. Do mask sensitive data before uploading.

       

       

      Best Regards,

      Lin

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

        Hi v-lili6-msft 

         

        Uploaded the file on dropbox link: https://www.dropbox.com/s/emirclmot8dwm3r/msdnpbix.pbix?dl=0

         

        In the report when I have one slicer “NewType” which contain the values (MeasureA,msdnMeasure) and one column chart.

         When I select  Newtype "msdnmeasure" value then column chart msdnmeasure value should show 3 not 4. Look like msdnmeasure measure applying the logic on filtered data set.

         

        Thanks,