cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
monk
Frequent Visitor

selecting all rows after a minimum value within a group.

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_nouser_idcampaign_iddate
1111null1/1/2021
21111012/1/2021
3111null3/1/2021
41111024/1/2021
51111035/1/2021
6111null6/1/2021
7111null7/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_nouser_idcampaign_iddate
21111012/1/2021
3111null3/1/2021
41111024/1/2021
51111035/1/2021
6111null6/1/2021
7111null7/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 @V-pazhen-msft 

1 ACCEPTED SOLUTION
V-pazhen-msft
Community Support
Community Support

@monk 

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. 

Vpazhenmsft_0-1636594933388.png

Measure =
var SelectedID_Date = CALCULATE(MAX([date]),FILTER(ALL('Table'),'Table'[campaign_id]=SELECTEDVALUE(campaign_id[Column1])))
Return IF(MAX([date])>=SelectedID_Date,"true")
Vpazhenmsft_1-1636595270006.png


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

 

View solution in original post

4 REPLIES 4
V-pazhen-msft
Community Support
Community Support

@monk 

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. 

Vpazhenmsft_0-1636594933388.png

Measure =
var SelectedID_Date = CALCULATE(MAX([date]),FILTER(ALL('Table'),'Table'[campaign_id]=SELECTEDVALUE(campaign_id[Column1])))
Return IF(MAX([date])>=SelectedID_Date,"true")
Vpazhenmsft_1-1636595270006.png


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

 

Thank you so much @V-pazhen-msft 

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?

 

monk_0-1636699132309.png

 

 

 

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")

 

 

 

 

monk
Frequent Visitor

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"))

 

 

monk
Frequent Visitor

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. 

 

Helpful resources

Announcements
June 2022 update 768X460.jpg

Check it out!

Click here to read more about the June 2022 updates!

Power Platform Conf 2022 768x460.jpg

Join us for Microsoft Power Platform Conference

The first Microsoft-sponsored Power Platform Conference is coming in September. 100+ speakers, 150+ sessions, and what's new and next for Power Platform.

Power BI Dev Camp Session 23 768x460.jpg

Check it Out!

Mark your calendars and join us on Thursday, June 30 at 11a PDT for a great session with Ted Pattison!

June 20 episode 7 with aka link 768x460.jpg

The Power BI Community Show

Join us on June 20 at 11 am PDT when Kim Manis shares the latest on Azure Synapse analytics, the Microsoft Intelligent Data Platform, and notable Power BI Updates from Microsoft Build 2022.

Top Solution Authors
Top Kudoed Authors