Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

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_no user_id campai...
  • Anonymous's avatar
    Anonymous
    4 years ago

    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.

     

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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.

 

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

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

     

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

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

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.