Power BI and Azure Data Platform

Different Granularities DAX pattern

Basic pattern example

In this example, the advertising expenditure is only known monthly, so the Total Advertising and Advertising% should not be shown at the daily level of sales data.
```
  [Total Advertising] =
    IF (
        NOT ( ISFILTERED ( 'Date'[Date] ) ),
        SUM ( Advertising[AdvertisingAmount] )
    )
```
Complete pattern example

Template:
- <unchecked measure> is the original measure, which ignores the granularity of the query.
- <invalid_granularity_column_N> is a column that has more detail (higher granularity) than the one available for the <unchecked measure>.
```
[Checked Measure] :=
    IF (
        NOT (
            ISFILTERED ( <invalid_granularity_column_1> )
            || ISFILTERED ( <invalid_granularity_column_2> )
            ...
            || ISFILTERED ( <invalid_granularity_column_N> )
        ),
        <unchecked measure>
    )
```
  In the Advertising table is a calculated column of fictitious dates for the monthly expenditure. This column is used to relate the Advertising and Date tables.
Simulate a relationship at different granularities example

In this example a YearMonth column is calculated in both the Advertising and Date tables.