Hi,
I am using Excel 2016 and I have two tables in my data model. Within one table, I am trying to do a DAX calculation where one of the conditions is that the column value in both tables are the same, basically an INDEX MATCH within a COUNTIF. The problem is, these column values are not unique in either table (many-to-many relationship). I can do the Excel formula quite easily (which can be found in the Desired Output sheet), I just want to replicate it in DAX.
I have tried the following, but it won't work as I don't have a related table. I would like to use the same structure if possible and replace the RELATEDTABLE function with something else so I can follow the logic. Would someone please be able to have a look at my Excel file and let me know what I need to use for this?
=
IF(
[Resource] = "SME1",
CALCULATE(
COUNTROWS(Table_Rev),
FILTER(
Table_Rev,
Table_Rev[SME1 Rem]=0
&&
Table_Rev[Consolidate]=FALSE()
),
RELATEDTABLE(Table_Rev)
)
,0)
Excel file
