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
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,

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

        vahi, joepath 

        I have a test on your sample pbix file, and find that why the msdnmeasure value shows 4 not 3.

        Filter the data manually by conditional.

        From the screenshot, you could find that "ORG_ENCOUNTER_KEY"=10013729, it has two rows data and it has different "USER_KEY", so in the calculation, it will be count by 2 not 1.

        If you want that when you select  Newtype "msdnmeasure" value then column chart msdnmeasure value should show 3 not 4.

        Please check that if you need to keep this conditional in your measure, if not, just remove the red part of the formula.

         

        msdnNewMeasure = 
var _table= SUMMARIZE('Review Metrics'
,'Review Metrics'[ORG_ENCOUNTER_KEY],'Review Metrics'[Activity Date],'Review Metrics'[USER_KEY]
,"Review"
,IF(CALCULATE(COUNTA('Review Metrics'[Review Type]))= CALCULATE(COUNTA('Review Metrics'[Review Type]),
    FILTER('Review Metrics','Review Metrics'[Review Type]="Additional Review")),1,2))
return
COUNTROWS(FILTER(_table,[Review]=1))

        Best Regards,

        Lin