topic Re: selecting all rows after a minimum value within a group. in DAX Commands and Tips

selecting all rows after a minimum value within a group.

Anonymous — Tue, 09 Nov 2021 17:51:54 GMT

Hi fellow Power BI developers,

Need help in creating DAX - calculated table. Please let me know if the below description is not clear.

I have data in the below format.

row_no	user_id	campaign_id	date
1	111	null	1/1/2021
2	111	101	2/1/2021
3	111	null	3/1/2021
4	111	102	4/1/2021
5	111	103	5/1/2021
6	111	null	6/1/2021
7	111	null	7/1/2021

Q: When campaign_id is selected in filter pane ex: 101, I would like to get all the rows from row_no 2 to 7 for user ID 111.

Expected output:

row_no	user_id	campaign_id	date
2	111	101	2/1/2021
3	111	null	3/1/2021
4	111	102	4/1/2021
5	111	103	5/1/2021
6	111	null	6/1/2021
7	111	null	7/1/2021

Here is the working SQL solution:

Select main_table.user_id, main_table.campaign_id, main_table.date From test_table as main_table Inner join (Select test_table.user_id, min(test_table.date) as min_date From test_table Where campign_id in (values selected in filter) group by test_table.user_id) as grouped_table on main_table.ID = grouped_table.user_id where main_table.date >= grouped_table.min_date Group by main_table.user_id, main_table.campaign_id, main_table.date

Query explanation: Group by user_id and get min(date) row within the selected campaign_id (from filter) rows. For each user ID, select all rows after min(date) [irrespective of campaign_id].

Other info:

Source: Direct Query (snowflake)

The above calculated table should be generated every time a date is selected in the slicer, new campaign Id is selected in the right filter pane.

Planning to use the above calculated table to create new measures like count rows, distinct rows, sum, etc.,

Please let me know if you need more info. I'm open to trying a different approach to get the same results.

v-robertq-msft Anonymous

Re: selecting all rows after a minimum value within a group.

Anonymous — Thu, 11 Nov 2021 01:48:30 GMT

Anonymous

The sample pbix is import mode otherwise you cannot access the data, but the steps should also work with Direct Query.

Since you are using direct query, you cannot create calculate table, instead you can add a distinct in power query. This table is used as slicer to filter your original table when comparing using dax.

Measure =

var SelectedID_Date = CALCULATE(MAX([date]),FILTER(ALL('Table'),'Table'[campaign_id]=SELECTEDVALUE(campaign_id[Column1])))

Return IF(MAX([date])>=SelectedID_Date,"true")

Paul Zheng _ Community Support Team
If this post helps, please Accept it as the solution to help the other members find it more quickly.

Re: selecting all rows after a minimum value within a group.

Anonymous — Wed, 17 Nov 2021 18:59:50 GMT

Thank you so much Anonymous

I tried your solution by modifying the measure a bit to get results for group by user_id.

Unfortunately, your solution returns true for rows which has no association with the selected campaign_id at all.

Please see the screenshot where user_id 333 is associated with only campaign Id 0 (null). Still the measure is returning true / 1 for campaign ID 102 selected from drop down.

I have created one more measure (count_measure) to get the count(unique user_id). It should be 2. Instead I see 3 because it is considering user_id 333 also.

is there a way we can get only user 111 and 222 as output and unqiue user_id count as 2?

Measure = var selected_campaigns = VALUES('Table'[campaign_id]) var SelectedID_Date = CALCULATE( MIN([date]), FILTER( ALL(Sheet1), --'Sheet1'[campaign_id]=SELECTEDVALUE('Table'[campaign_id]) Sheet1[campaign_id] IN selected_campaigns && Sheet1[user_id] = SELECTEDVALUE(Sheet1[user_id]))) Return IF((MIN(Sheet1[date])>=SelectedID_Date),"TRUE", "FALSE")

Re: selecting all rows after a minimum value within a group.

Anonymous — Sat, 13 Nov 2021 07:21:03 GMT

I figured out how to get only user_ids which are relevant to selected campaign_id

added user_ids to unique campaign_id table.

Measure = var selected_campaigns = VALUES('Table'[campaign_id]) var selected_users = VALUES('Table'[user_id]) var SelectedID_Date = CALCULATE( MIN([date]), FILTER( ALL(Sheet1), --'Sheet1'[campaign_id]=SELECTEDVALUE('Table'[campaign_id]) Sheet1[campaign_id] IN selected_campaigns && Sheet1[user_id] = SELECTEDVALUE(Sheet1[user_id]))) Return IF((MIN(Sheet1[date])>=SelectedID_Date) && (min(Sheet1[user_id]) IN selected_users),"TRUE", "FALSE")

unique_user_count = CALCULATE(DISTINCTCOUNT(Sheet1[user_id]), FILTER(Sheet1, Sheet1[Measure] = "TRUE"))

Re: selecting all rows after a minimum value within a group.

Anonymous — Wed, 17 Nov 2021 19:55:27 GMT

FYI - if the source is Direct Query and the data size is huge, it will throw a "resultset exceeds 1M rows" error. This logic tries to bring the complete data to the analysis service first before applying DAX.

A workaround is to keep this in a custom SQL query while connecting to the data source.