Forum Discussion

raj111983's avatar
raj111983
Frequent Visitor
1 year ago
Solved

Help with Intersect Function

HI, I just new to Power BI and having difficult time in using Intersect function. Basically I have a single Sales table with multi year data. I created an unrelated Dates table from the Sales  table ...
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi raj111983 ,

    I updated your sample pbix file(see the attachment), please check if that is what you want.

    Chart PTran = 
VAR _selyear =
    SELECTEDVALUE ( 'Dates'[Date].[Year] )
VAR _prevyear =
    SELECTEDVALUE ( 'Dates'[Date].[Year] ) - 1
VAR _month =
    SELECTEDVALUE ( 'Sales'[Date].[Month] )
VAR _CSource =
    CALCULATETABLE (
        VALUES ( 'Sales'[Source ID] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Date].[Year] = _selyear )
    )
VAR _PSource =
    CALCULATETABLE (
        VALUES ( 'Sales'[Source ID] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Date].[Year] = _prevyear )
    )
VAR _tab =
    INTERSECT ( _CSource, _PSource )
RETURN
    CALCULATE (
        SUM ( Sales[Measure Value] ),
        FILTER (
            'Sales',
            'Sales'[Date].[Year] = _prevyear
                && 'Sales'[Date].[Month] = _month
                && 'Sales'[Measure Name] = "Volume"
                && 'Sales'[Source ID] IN _tab
        )
    )

    Best Regards

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi raj111983 ,

I updated your sample pbix file(see the attachment), please check if that is what you want.

Chart PTran = 
VAR _selyear =
    SELECTEDVALUE ( 'Dates'[Date].[Year] )
VAR _prevyear =
    SELECTEDVALUE ( 'Dates'[Date].[Year] ) - 1
VAR _month =
    SELECTEDVALUE ( 'Sales'[Date].[Month] )
VAR _CSource =
    CALCULATETABLE (
        VALUES ( 'Sales'[Source ID] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Date].[Year] = _selyear )
    )
VAR _PSource =
    CALCULATETABLE (
        VALUES ( 'Sales'[Source ID] ),
        FILTER ( ALLSELECTED ( 'Sales' ), 'Sales'[Date].[Year] = _prevyear )
    )
VAR _tab =
    INTERSECT ( _CSource, _PSource )
RETURN
    CALCULATE (
        SUM ( Sales[Measure Value] ),
        FILTER (
            'Sales',
            'Sales'[Date].[Year] = _prevyear
                && 'Sales'[Date].[Month] = _month
                && 'Sales'[Measure Name] = "Volume"
                && 'Sales'[Source ID] IN _tab
        )
    )

Best Regards

  • raj111983's avatar
    raj111983
    Frequent Visitor
    1 year ago

    Thank you!! Worked Perfectly!!