Creating cross join functionality

[sanc_152]

Hi  ,  @Ahmedx , @Greg_Deckler , @amitchandak Please can you help on this scenario.

I am dealing with problem beacuse of auto exist functionality in PBI
I have 3 tables. Product and customer are 2 dimension tables.
And 1 fact Table .
When I M putting column from 3 tables in Matrix chart it is generating cross join result whenever I am trying to put if condition.

I want to calculate win mesure which dynmically generate win or loss result from columns 1stforecast and 2ndforecast.
1.whichever is closest to sales is winner 
2.if sales is null or 0 then result will be "NA"
3. if sales is 0 or null but one of the vlaues is present from 1stforecast and 2ndforecast then winner will be the column who having values.

Fact table:

Csutomer	Product	Sales	1stforecast	2ndtforecast
ABC	1	10	20	22
BAC	2	11	21	15
ABC	3	12	22	13
ABC	4	13	23	20
DA	5	0	24
DA	6		25	26

Customer table

Customercode	Description
ABC	Soap
BAC	nutrition
ABC	Soap
ABC	Soap
DA	Tyre
DA	Tyre

Prdouct table:

Product Key	Product description
1	A
2	b
3	c
4	d
5	e

[Anonymous]

Hi @sanc_152 ,

My measure:

Winner = 
VAR _SALES =
    SUM ( 'Fact table'[Sales] ) + 0
VAR _FORECAST1 =
    SUM ( 'Fact table'[1stforecast] )
VAR _FORECAST2 =
    SUM ( 'Fact table'[2ndtforecast] )
VAR _DIFF1 =
    ABS ( _SALES - _FORECAST1 )
VAR _DIFF2 =
    ABS ( _SALES - _FORECAST2 )
RETURN
    IF (
        HASONEVALUE ( 'Fact table'[Csutomer] ),
        IF (
            _SALES = 0,
            IF (
                ISBLANK ( _FORECAST1 ),
                IF ( ISBLANK ( _FORECAST2 ), "NA", "2ndforecast" ),
                IF ( ISBLANK ( _FORECAST2 ), "1stforecast", "NA" )
            ),
            IF (
                _DIFF1 = _DIFF2,
                "NA",
                IF ( _DIFF1 < _DIFF2, "1stforecast", "2ndforecast" )
            )
        )
    )

Matrix:

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[sanc_152]

@Anonymous 
Thanks ,Great it is me giving me the result I want .But need one more if condition :

when actuals is 10 ,1stforecast=0 and 2ndforecast=30 then 2ndforecast should be winner .
Can we add this condition ?