Forum Discussion

svishwanathan's avatar
svishwanathan
Helper III
7 years ago
Solved

Dax help-using variables

Hi   I want to create a measure that adds the sales in a given period. There is a column in the sales/actuals table called #Day which is a lookup from my calendar table. This column contains a uniq...
Need Help
  • v-jiascu-msft's avatar
    v-jiascu-msft
    7 years ago

    Hi svishwanathan,

     

    The Segment table should be independent of other tables, which means no relationship can be established. And also try to wrap Actuals with ALL like below.

    ActualsSegment =
VAR lowerlimit =
    IF ( HASONEVALUE ( 'Segment'[Segment] ), VALUES ( 'Segment'[Min] ), BLANK () )
VAR higherlimit =
    IF ( HASONEVALUE ( 'Segment'[Segment] ), VALUES ( 'Segment'[Max] ), BLANK () )
RETURN
    CALCULATE (
        [Actuals],
        FILTER (
            ALL ( Actuals[#Day] ),
            Actuals[#Day] >= lowerlimit
                && Actuals[#Day] <= higherlimit
        )
    )

    Best Regards,

    Dale

v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
7 years ago

Hi svishwanathan,

 

The Segment table should be independent of other tables, which means no relationship can be established. And also try to wrap Actuals with ALL like below.

ActualsSegment =
VAR lowerlimit =
    IF ( HASONEVALUE ( 'Segment'[Segment] ), VALUES ( 'Segment'[Min] ), BLANK () )
VAR higherlimit =
    IF ( HASONEVALUE ( 'Segment'[Segment] ), VALUES ( 'Segment'[Max] ), BLANK () )
RETURN
    CALCULATE (
        [Actuals],
        FILTER (
            ALL ( Actuals[#Day] ),
            Actuals[#Day] >= lowerlimit
                && Actuals[#Day] <= higherlimit
        )
    )

Best Regards,

Dale