DAX Filter table with Many to Many and many to one relationship

[DJ_Paxton]

Hi all, I'm new to Power BI and I'm trying to create a measure with multiple filters and a few relationships.

Thats how my model view looks like at the moment. And I'm trying to count Audits based on DateAuditDue as one filter, and then the AccreditationCategory in CompanyInfo table should match CategoryName in AccreditationCategories table.

The table CompanyInfo doesn't have CategoriesID, that's why I need to match strings and for doing so I need to go through ProgramAccreditationCategories table, which has a many to many relationship with CompanyInfo.

This may seem overcomplicated, but in SQL would be something like:

select * from AuditHeader

join CompanyInfo C on CompanyID

join ProgramAccreditationCategories PAC on ProgramGUID

join AccreditationCategories AC on PAC.OverdueAccreditationCategory = AC.Id

where C.AccreditationCategory = AC.CategoryName

So the joins would be automatically filtered.

In DAX I have this:

OverdueAudits =

    CALCULATE(

        _mAuditHeader[AuditCount], //measure that counts audits

        CompanyInfo[DateAuditDue] < TODAY(),

        FILTER(

            ALL(CompanyInfo[AccreditationCategory]),

            CompanyInfo[AccreditationCategory] IN RELATED(AccreditationCategories[CategoryName])

        )

    )

Which gives me The column CategoryName doesn't exist or doesn't have a relationship, so I'm assuming I'm missing the joins.

I tried relating ProgramAccreditationCategory with Programs table, to avoid the Many to Many relationship by having many to one - one to many, but at this point I don't see any advantage in doing that.

I'd really appreciate some clarification with this, I'm probably overcomplicating things.

Thanks!

[TomMartens · Accepted solution]

Hey @DJ_Paxton ,

first, welcome to Power BI!

Then, ...

There are at least two things that you have to consider iin your measure.

1. RELATED returns a single value not a table, you can read more about RELATED in this article: RELATED – DAX Guide
2. IN checks if a value is contained in a set of values, the set of values is configured as a table: The IN operator in DAX - SQLBI

Additionally, due to the many-to-many relationship columns from the one-side (no matter of the depth of the relationships) are not extended to the table of the many side of the relationship. For this reason you have to write your measure slightly different. You can read about the concept of the extended table here: Extended tables - the sword by my side - Mincing Data - Gain Insight from Data (minceddata.info)

Consider rewriting the table filter of your measure like so:

measure = 
CALCULATE(
...,
CALCULATETABLE(
ALL( CompanyInfo[AccreditationCategory]),
TREATAS( VALIUES(AccreditationCategories[CategoryName]), CompanyInfo[AccreditationCategory] )
)

Read about TREATAS here: TREATAS – DAX Guide

What it does it passes a table to a column, and then values of the table are used to filter the table.

If this does not work, consider to create a pbix file that contains sample data but still reflects your semantic model (tables, relationships, calculated columns, and measures) upload the pbix to OneDrive, Google Drive, or Dropbox and share the link. If you are use a spreadsheet to import the sample data instead of using the manual input method share the spreadsheet as well.

Hopfully, this helps to tackle your challenge.

Regards,

Tom

[RossEdwards]

First thing to mention is that when you call a measure, you don't call the 'home table' of the measure.  So you can change _mAuditHeader[AuditCount] to just [AuditCount]

Next thing to consider. Power BI works best using a Star Schema.  Typically if you find yourself with a many to many relationship, you have made a modelling mistake and need to reconsider your approach.  Power BI is not a database, so you want to denormalise where possible and get back to a star schema (or if necessary snowflake).