Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

DAX Measure SelectedValue in Values, how to use inactive relationship filter

Hi, I have a Fact Table of incidents, and a date table.  An Incident has an Occur Date Key, an Assigned Date Key, an Employee who entered the Incident into the system, and an Employee to whom the incident was assigned.  The active relationship (that  must remain active) is the Occur Date.  Employee who entered the Incident data and Employee to whom Incident is assigned are both inactive relationships to my Personnel table.

My slicers on the report are Date range and Employee Number.

I have a Measure on a card that shows how many overdue incidents a selected employee has:

Employee Overdue Incidents = CALCULATE(DISTINCTCOUNT(Incident_Table[Incident_ID]),Incident_Table[overdue_flag] = "Y", USERELATIONSHIP(Incident_Table[DateKey_Assigned], DateList[DateKey]), USERELATIONSHIP(Incident_Table[Employee_Assigned], Personnel[Emp_Num])) + 0

And this is working fine. 

 

However, I also need to show a Table visual list of Overdue Incidents for a given employee. 

Originally I tried to create a Measure that would "mark" a record with a "1" if the employee selected was assigned to the incident:

EmpAssigned = IF(SELECTEDVALUE(Personnel[Emp_Num]) in VALUES(Incident_Table[Employee_Assigned]), 1, 0)

Then I was going to filter the report table on the EmpAssigned=1 and only showing those incidents?

But I need this Measure to use DateKey_Assigned (NOT DateKey Occur, which is the active relationship), and I will also have to include Incident_Table[overdue_flag] = "Y"   as well to show only Overdue incidents.

I'm not sure how to update my EmpAssigned Measure to include the USERELATIONSHIP for DateKey_Assigned and to Filter for overdue_flag = "Y"?   Am I even on the right track of how to show a list of Overdue Incidents for a selected employee in a selected date range?

Thanks for any help!

1 ACCEPTED SOLUTION

I ended up creating a calculated table:


Overdue_Incident_Table = SELECTCOLUMNS(FILTER(Incident_Table, Incident_Table[overdue_flag] = "Y"), "Case Number", Incident_Table[Incident_ID], "Assigned Date Key", Incident_Table[DateKey_Assign], "Assigned To", Incident_Table[Employee_Assigned])

 

Then I linked the "Assigned Date Key" from this table to my DateList table, and the "Assigned To" to my Personnel table and it works for what I need.

I'd still like to know if there's an easier/better way to accomplish this without resorting to creating a whole other table...?

View solution in original post

9 REPLIES 9

Actually, I can use the filter overdue_flag = "Y" as a filter on the visual itself, so I can leave that part out of the EmpAssigned Measure calculation.  But  I do still need the Measure to take into account the inactive DateKey_Assigned relationship when filtering the data, and disregard the active DateKey_Ocurred relationship. 

Currently when I select 1/1/2019 to 3/1/2019 as date filters, it is pulling in records where Occur Date falls within this range, but Assigned Date does not.  I need it to pull records Assigned between my selected dates, not Occurred.  Thanks for any help! 

Hi @PowerBIkalaimo ,

 

We can create a measure with following formula and put with into visual Filter (set the condition as greater than zero) , if the table visual have Incident_ID in each rows, it should work.

 

EmpAssigned =
CALCULATE (
    COUNTROWS ( 'Incident_Table' ),
    FILTER (
        'Incident_Table',
        AND (
            'Incident_Table'[Employee_Assigned] IN FILTERS ( Personnel[Emp_Num] ),
            'Incident_Table'[overdue_flag] = "Y"
        )
    )
)

If it doesn't meet your requirement, kindly share your sample data and expected result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Hi, thanks, unfortunately there is confidential information in my report so I can't share the pbix file.  What I'm expecthing though, here is my SQL: 

Select distinct(IncidentNumber) from Incident_Table
where  DateKey_Assigned between 1462 and 1551 and overdue_Flag = 'Y' and Employee_Assigned = '00123456'

This produces a list of 7 Incidents for this Employee that were assigned between 1/1/2019 and 3/31/2019 that are overdue.

Incidents:  1122, 1133, 1144, 1155, 1166, 1177, 1188

 

If I run the query below in DAX, and set the visual table filter to EmpAssigned > 0, I get 3849 records (more than if I look at all the records in SQL between those dates, so I'm not sure what's happening here).  

 

If I add USERELATIONSHIP for the date keys like below, I get 7 records but these are a DIFFERENT 7 IncidentNumbers than in the SQL query above. (If I query in SQL using  IncidentNumbers from the DAX above, I find records  where Assigned Employee is NOT the selected employee):

EmpAssigned =
CALCULATE (
    COUNTROWS ( 'Incident_Table' ),
    FILTER (
        'Incident_Table',
        AND (
            'Incident_Table'[Employee_Assigned] IN FILTERS ( Personnel[Emp_Num] ),
            'Incident_Table'[overdue_flag] = "Y"
        )
    )
    USERELATIONSHIP(Incident_Table[DateKey_Assigned], DateList[DateKey])
)

Incidents:  2233, 4466, 5577, 2211, 7788, 4455, 2299 - if I look these up in SQL, the selected employee was NOT assigned to these records, so I have no idea what's happening here.

 

What I need is a way to do the SQL statement at the top, where Employee Number and Date Keys are fed in by the slicers.  I need a list of records where the Selected Employee was assigned to the record, and the Assigned Date of the record falls between the 2 dates from the date slicer.  Using the above values, I should be able to produce a table visual with the same 7 records as I do from the SQL query.

 

Both Assigned Date to Date List table, and Assigned Employee to Personnel table are Inactive relationships in my report.

It's frustrating that it's so easy to do in SQL, but I'm just not getting how to do it in DAX...

 

Thanks for your help!

 

Hi @PowerBIkalaimo ,

 

Could you please try to create a calculated table with following formula to see if you can get the result of 1122, 1133, 1144, 1155, 1166, 1177, 1188?

 

EmpAssignedTestTable =
DISTINCT (
    FILTER (
        'Incident_Table',
        AND (
            AND (
                'Incident_Table'[Employee_Assigned] = "00123456",
                'Incident_Table'[overdue_flag] = "Y"
            ),
            AND ( DateKey_Assigned >= 1462, DateKey_Assigned <= 1551 )
        )
    )
)

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Also, I came up with this Measure that I thought would work for me:

EmpAssigned = IF(CALCULATE(SELECTEDVALUE(Personnel[Emp_Num]) in VALUES(Incident_Table[Employee_Assigned]), USERELATIONSHIP(Incident_Table[DateKey_Assigned], DateList[DateKey])), 1,0)

 

But when I ran it, it was still using the active DateKey_Occur instead of DateKey_Assigned. 

 

As an experiment, I made the DateKey_Occur relationship inactive, and then the measure above worked.

 

So is there a way to force the above to use the DateKey_Assigned relationship (when the required DateKey_Occur relationship is active)  that I'm not doing?  I'm not understanding why it uses the active relationship rather than the relationship I'm telling it to use??  And if I turn the active relationship off, THEN it uses the relationship I'm telling it to use (ie when I have both DateKey relationships as inactive it works).

 

Thanks for all your help with this.

Hi @PowerBIkalaimo ,

 

When the relation between Occur Date Key and DateList[DateKey] is active, the table visual had been filter by this relation, so there even put the relation in measure, the ID in table is not all the IDs, We think We can crease a sperate calculated table using following measure and put into the table visual.

 

DistinctIDTable =
DISTINCT ( Incident_Table[Incident_ID] )

 

Then we create a measure into the visual filter

 

EmpAssignedTestMeasure =
COUNTROWS (
    FILTER (
        ALL ( 'Incident_Table' ),
        'Incident_Table'[Incident_ID] IN FILTERS ( DistinctIDTable[Incident_ID] )
            && 'Incident_Table'[overdue_flag] = "Y"
            && 'Incident_Table'[Employee_Assigned] IN FILTERS ( Personnel[Emp_Num] )
            && 'Incident_Table'[DateKey_Assigned] IN FILTERS ( DateList[DateKey] )
    )
) + 0

 

 

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

I created the Distinct ID table and tried the EmpAssignedTestMeasure and it returned all the rows.  I even tried coding in some of the values like this:

 

EmpAssignedTestMeasure =
COUNTROWS (
    FILTER (
        ALL ( 'Incident_Table' ),
        'Incident_Table'[Incident_ID] IN FILTERS ( DistinctIDTable[Incident_ID] )
            && 'Incident_Table'[overdue_flag] = "Y"
            && 'Incident_Table'[Employee_Assigned] = SELECTEDVALUE(Personnel[Emp_Num])
            && 'Incident_Table'[DateKey_Assigned] >= 1462 && 'Incident_Table'[DateKey_Assigned] <= 1551
    )
) + 0

 

But it still is returning all rows, ignoring the DateKey altogether (showing me records dated after 3/31/2019 which is datekey 1551).

I ended up creating a calculated table:


Overdue_Incident_Table = SELECTCOLUMNS(FILTER(Incident_Table, Incident_Table[overdue_flag] = "Y"), "Case Number", Incident_Table[Incident_ID], "Assigned Date Key", Incident_Table[DateKey_Assign], "Assigned To", Incident_Table[Employee_Assigned])

 

Then I linked the "Assigned Date Key" from this table to my DateList table, and the "Assigned To" to my Personnel table and it works for what I need.

I'd still like to know if there's an easier/better way to accomplish this without resorting to creating a whole other table...?

Hi, yes, I get the expected rows in this calculated table...

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.