Dax function RELATED - How Do I Apply It?

[Txtcher]

I have the following measure:

Licensing NA Approved Apps = CALCULATE(
            Min(HX[HXDate]),RELATED(Users[Id]),
            HX[NewValue] = "Approved")

Works perfectly. But now I need to limit the results to only those where HX[CreatedByID] = the [Id] field from the Users table.

The Users table has a one-to-many relationship with the HX table.

I've tried several different ways to add in the DAX function RELATED to this measure, because I think that is what I need here, but am having no luck.

If I need to provide more information, please let me know.

[DataNinja777]

Hi @Txtcher ,

The issue with your measure is that RELATED does not work inside a measure the way you're trying to use it. RELATED is meant for calculated columns, where row context exists. In a measure, you're operating in filter context, so instead of using RELATED, you need to compare directly within the CALCULATE function. If you’re trying to filter the HX table where HX[CreatedByID] matches the current Users[Id], and your visual is grouped or filtered by Users[Id], the correct way is to use SELECTEDVALUE(Users[Id]) to capture that context.

Licensing NA Approved Apps = 
VAR CurrentUserId = SELECTEDVALUE(Users[Id])
RETURN
CALCULATE(
    MIN(HX[HXDate]),
    HX[NewValue] = "Approved",
    HX[CreatedByID] = CurrentUserId
)

This works because SELECTEDVALUE retrieves the current Users[Id] in the visual or filter context, and the filter HX[CreatedByID] = CurrentUserId properly restricts the HX table to only the relevant rows.

Best regards,

[Txtcher]

Oh thank you so much for explaining that RELATED is for calculated columns.

However, the visual is not grouped or filtered by User[Id] so I am not sure how to apply the measure you provided. If I remove the original measure I created (see op), and replace it with the one you gave me, the table visual goes blank. The only way to get it to display data is to add a column from Users. Which I suppose is okay, but it not necessary to display it. I tried clicking the hide icon in filters, but it does not disappear in the visual. (I am very new to Power Bi.)

Next problem: How to fix the card visual that counts the number of rows in the table visual on the same page? In other words, how do I incorporate the User ID filter? Here is the original measure for the card visual:

Total NA Apps Approved = COUNTROWS(
    SUMMARIZE(
        FILTER('NA Apps',
          [Approved]),'NA Apps'[Id]
            ))

I tried adding the new mesure to it in the Filter argument but it displays blank. If I am understanding correctly, it returns a blank because the NA Apps table does not have a relationship with the Users table? It is related to the HX table in a 1 to many relationship.

Total NA Apps Approved = COUNTROWS(
    SUMMARIZE(
        FILTER('NA Apps',
          [Licensing NA Approved Apps]),'NA Apps'[Id]
            ))

Here are the relationships:

 Thank you in advance.

[Txtcher · Accepted solution]

After doing some due diligence (reading and learning) about Row Context, Filter Context, Calculate, thanks to videos frm SQLBI, I think I have a better grasp of how the slicers and visuals work.

I think I fixed the problem without writing any DAX. 

I simply added a filter to the entire page for User[Id] is not Blank. That filtered my table visual and the card visual. This works because the goal is to demonstrate how many applications were approved for the selected date, but only those approved by a User from the User table. There will never be a business reason to include those that are approved by any entity outside of the User table.

My thanks & my apologies to @DataNinja777 . It appears I messed up my OP.  The original measure to count the rows in the table visual was this:

Total NA Apps Approved = 

COUNTROWS(
    SUMMARIZE(
        FILTER('NA Apps',
          [Approved]),
          'NA Apps'[Id]
    )
)

I failed to communicate that I needed a count of all approved apps by users only. Not counts of how many apps approved by individual users.

But that would be a good slicer to add for management overview.

Thank you for your response.