composite models issue with dax

[wpf_]

I have a composite model: directquery tables/import table

In one measure I am filtering the directquery table by the employee name (from a import table).  But it seems it does not come up with anything. 

--------------------------------------------------

For example: 

Var Employee = minx(employee, employee[employeename])     //import table

Var LeaveTable =   //directquery table

Filter(

Summarize(

LeaveTable,

StartDate,

EndDate,

Employee

),

Employee = Employee

)

------------------------------------------

Nothing comes up.  I read this from website: cross-source relationships have different behavior. You can't use Data Analysis Expressions (DAX) functions to retrieve values on the one side from the many side.  And i believe this is the case for me.  Is there a workaround for this?  Thanks 

[v-yalanwu-msft]

Hi, @wpf_ ;

I tested it ,you should change Directquery to Daul model ,because it you have filter it from relationship.As this video says

https://www.youtube.com/watch?v=C0nHSaz5ITg

then create a measure.

Measure2 = MIN([RequestNumber])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wpf_]

@v-yalanwu-msft Thanks for your help, I am able to use the dax functions with dual mode, but unfortunately I dont want to make certain DQ tables cached, because I need to show the data when there are new or updated data in the data source.  Refreshing data takes too long.  

So without doing dual mode, I suppose there is no other workaround?  

[v-yalanwu-msft]

Hi, @wpf_ ;

Sorry, I haven't thought of other methods at the moment, I have also tried.or you could try change import model to DQ mode.

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wpf_]

@v-yalanwu-msft thanks for your help anyway.  At least I learned about dual mode tables.  

[v-yalanwu-msft]

Hi, @wpf_ ;

It should be noted that the table I created is not measure. Could you please share the screenshot of the prompt that you don't work, or provide the sensitive information removed by PBIx for further discussion?
Best Regards,
Community Support Team_ Yalan Wu

[wpf_]

@v-yalanwu-msft 

Here is a pbix file i created.  It is an oversimplify example.  The file contains two imported tables that work.  Please change the employee table to directquery, and it will stop working.  Thanks.

https://drive.google.com/file/d/1ZGUk_qAVyQgy6WGCX_rTcYECYWdJXGyA/view?usp=sharing

[wpf_]

@v-yalanwu-msft 

Since my file is a directquery/import it is hard to share with you.  So here is a detail explanation:

I have two tables (one directquery and one import table):

Here is how the report looks like:

Here is how the Choose Employee Measure Looks like:

Choose Employee Measure:=

Var SlicerDate= DATEVALUE(Calendar[Date Slicer])

VAR DayOfWeek = WEEKDAY(SlicerDate)

VAR Employee_Table =

FILTER(

SUMMARIZE(

employee,

employee[employeename],

employee[workzone],

employee[offday],

employee[replacement]

),

employee[offday] = DayOfWeek

)

Var dayoff = MINX(Employee_Table, Employee_Table[offday])

Var dayOffReplacement =  MINX(Employee_Table, Employee_Table[replacement])

VAR dayOffReplacementCheck = IF(dayoff = DayOfWeek, dayOffReplacement, blank())

Var getDayoffEmployee =

IF(dayOffReplacementCheck = BLANK(),

"No dayoff replacement",

dayOffReplacementCheck

)

VAR Vacation_Table = 

FILTER(

SUMMARIZE(

vacation,

vacation[Employee name],

vacation[vacation number],

vacation[start date],

vacation[end date],

vacation[createdon]

),

vacation[Employee name] = getDayoffEmployee &&

vacation[start date] <= SlicerDate &&

vacation[end date] >= SlicerDate

)

Var LatestCreateDate = MAXX(vacation, vacation[createdon])

So i will not include the rest of the measure code.  But i am basically going to use the LatestCreateDate  variable to go calculate something else.  But the problem is in the filter for the vacation_table variable: vacation[Employee name] = getDayoffEmployee.  It is not seeing the variable getDayoffEmployee when it is used in the vacation_table variable.  But if i return just the getDayoffEmployee variable, it returns correctly.  Only when it is used in the vacation_table variable it does not see it.  I read that because it is a mixed mode tables, i cannot use dax functions from the one side of the table.  In this case it is the employee table.  But i tried making it a many to many relationship and it still does not work.  I also tried making it it a both directions for the cross filter direction.  When i have all the tables from directquery the measure works fine.  Hope you can help. Thanks. 

[v-yalanwu-msft]

Hi, @wpf_ ;

Your dax don't  have RETURN only have VAR ,so it doesn't right. and you could create a table like below:

Fliter = VAR _em=SUMMARIZE('employee',[employeename])
return SUMMARIZE(FILTER('LeaveTable',[Employee] in _em),[StartDate],[Employee],[EndDate])

The final output is shown below:

Best Regards,
Community Support Team_ Yalan Wu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[wpf_]

@v-yalanwu-msft Thanks but that did not work.