Ignore grouping if value is found

[Anonymous]

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

[Anonymous]

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]

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]

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. 

[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-msft]

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]

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]

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]

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]

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

[joepath]

@v-lili6-msft  After the change when I unselect "msdnmeasure" filter then it gives the count 1 not 3, so in both the condition whether we select or not it should give the result 3.

[v-lili6-msft]

hi, @joepath 

If you want this measure?

msdnNewMeasure = 
var _table= SUMMARIZE('Review Metrics'
,'Review Metrics'[ORG_ENCOUNTER_KEY],'Review Metrics'[Activity Date],'Review Metrics'[NewType]
,"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))

and you could use this formula to add a new table to check what is the aggregate conditions of your requirement

Table = FILTER(SUMMARIZE('Review Metrics'
,'Review Metrics'[ORG_ENCOUNTER_KEY],'Review Metrics'[Activity Date],'Review Metrics'[USER_KEY],'Review Metrics'[NewType]
,"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)),'Review Metrics'[ORG_ENCOUNTER_KEY] in {10013730,10013728,10013724,10013746,10013729,10013726})

Best Regards,

Lin

[joepath]

@v-lili6-msft  This seems close to be what i am looking for, still has some issue while applying the other filter like dimuser.

I will work on that,

Thanks for your help.

[v-lili6-msft]

hi, @joepath 

For the different conditional filter, the summary table will get different result.

You could use the same way as I provided to add a new table to check the logic.

Hope it could help you.

Best Regards,

Lin

[joepath]

@v-lili6-msft  Or can we write a logic something like, create a flag in the review table and based on the grouping (activity date,user key,and org encounter key) if in the group any value found with Initial review then update all the additional review row flag with 0 and if we don't find any value in the group then update the flag with 1, and in the calculation we can use that flag to count the additional review. that will not be affected by the filter condition.

In Dax what are the function we can use to archive this?