Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
1 year ago
Solved

Difference when some Match and some do not

I have data like this: Date  Area  Continent Sold 2025-12-28 Central Asia 5 2025-12-28 East Asia 6 2025-12-28 West Asia 8 2025-12-28 Central America 4 2025-12-28 E...
DAX
  • VahidDM's avatar
    VahidDM
    1 year ago

    Use a 2-dimension (row/column) setup and one measure.

    1) Create a location dim and a copy (for columns)

    DimLoc =
    DISTINCT ( SELECTCOLUMNS ( Fact, "Continent", Fact[Continent], "Area", Fact[Area] ) )

    DimLoc_Col = DimLoc


    Relate Fact[Continent],[Area] → DimLoc[...] and Fact[...] → DimLoc_Col[...] (both many-to-one, active). Keep your Date table slicer linked to Fact.

    2) Measure for the matrix
    Put DimLoc on Rows and DimLoc_Col on Columns, then use:

    Diff :=
    VAR sameContinent =
    SELECTEDVALUE(DimLoc[Continent]) = SELECTEDVALUE(DimLoc_Col[Continent])
    VAR sameArea =
    SELECTEDVALUE(DimLoc[Area]) = SELECTEDVALUE(DimLoc_Col[Area])
    RETURN
    IF (
    NOT sameContinent, BLANK(),
    IF (
    sameArea, BLANK(),
    VAR rowSold =
    CALCULATE ( SUM ( Fact[Sold] ), ALL ( DimLoc_Col ) ) -- only row loc
    VAR colSold =
    CALCULATE ( SUM ( Fact[Sold] ), ALL ( DimLoc ) ) -- only column loc
    RETURN rowSold - colSold
    )
    )


    This returns blank when continents differ or areas match; otherwise it shows the difference for the selected date.

GeraldGEmerick's avatar
GeraldGEmerick
Icon for Memorable Member rankMemorable Member
1 year ago

Anonymous You will need to create an urelated table like the following:

Table 2 = SELECTCOLUMNS( 'Table', "Continent", [Continent], "Area", [Area] )

 

Use this as either the rows or columns in the matrix and use the Country and Area from the original table as the other (row/column). You can then use a measure like the following:

Measure = 
    VAR _Continent1 = MAX( 'Table'[Continent] )
    VAR _Area1 = MAX( 'Table'[Area] )
    VAR _Continent2 = MAX( 'Table 2'[Continent] )
    VAR _Area2 = MAX( 'Table 2'[Area] )
    VAR _Date = MAX( 'Table'[Date] )
    VAR _Return =
        SWITCH( TRUE(),
            _Continent1 <> _Continent2, BLANK(),
            _Area1 = _Area2, BLANK(),
            SUM( 'Table'[Sold] ) - CALCULATE( SUM( 'Table'[Sold] ), FILTER( ALL( 'Table'), 'Table'[Date] = _Date && 'Table'[Continent] = _Continent2 && 'Table'[Area] = _Area2 ) )
        )
RETURN
    _Return