Forum Discussion

MDR's avatar
MDR
Frequent Visitor
3 years ago
Solved

Conditional SUM

I used the following DAX to return total "Approved" amounts across all datanameshorts in my dfact table. When datanameshort is either "SBG" or "SBG10" the total of "Approved" and "Paid" amounts must be returned instead.

As datanameshort is a column I needed to use selectedvalue to get a single value out of all values in the column and looks like that makes the condition to be always False (only else part gets executed)

ApprovedAmount =
IF (
CONTAINSSTRING(SELECTEDVALUE(DFACT[DATANAMESHORT]), "SBG"), CALCULATE(SUM(DFACT[AMOUNT]), FILTER(DFACT, CONTAINSSTRING(DFACT[DATANAMESHORT],"SBG") && DFACT[STATUS] IN {"Approved","Paid"} && DFACT[GEONAME] <> BLANK())),
CALCULATE(SUM(DFACT[AMOUNT]), FILTER(DFACT, DFACT[STATUS] = "Approved")))

 

Does anyone have any idea how to fix this.

a screenshot of the dfact table is added 

 

" Slicer"
"CALCULATE"
multiple filters
sum
  • johnt75's avatar
    johnt75
    3 years ago

    The problem is that there is more than value for data name short, so SELECTEDVALUE will return blank.

    Try

    ApprovedAmount =
IF (
    NOT ISEMPTY ( INTERSECT ( VALUES ( DFACT[DATANAMESHORT] ), { "SBG", "SBG10" } ) ),
    CALCULATE (
        SUM ( DFACT[AMOUNT] ),
        DFACT[DATANAMESHORT]
            IN { "SBG", "SBG10" }
                && DFACT[STATUS]
                    IN { "Approved", "Paid" } && NOT ISBLANK ( DFACT[GEONAME] )
    ),
    CALCULATE ( SUM ( DFACT[AMOUNT] ), DFACT[STATUS] = "Approved" )
)

4 Replies

Replies have been turned off for this discussion
  • johnt75's avatar
    johnt75
    Super User
    3 years ago

    How are you using the measure? Is it in a visual which includes the data name short column or is it just in aggregation ?

    • MDR's avatar
      MDR
      Frequent Visitor
      3 years ago

      I have it in a table with datname as below:

       

      Small Business Disaster Recovery Grants represents both "SBG" and "SBG10".

      I've used a similar aggregated measure in a card without any issues but in the table instead of getting the sum of "Approved" and "Paid" for "Small Business Disaster Recovery Grants" I am getting only "Approved".

      The aggregated mesure is as follows:

      CALCULATE(SUM(DFACT[AMOUNT]), FILTER(DFACT, CONTAINSSTRING(DFACT[DATANAMESHORT],"SBG") && DFACT[STATUS] IN {"Approved", "Paid"} && DFACT[GEONAME] <> BLANK())
      • johnt75's avatar
        johnt75
        Super User
        3 years ago

        The problem is that there is more than value for data name short, so SELECTEDVALUE will return blank.

        Try

        ApprovedAmount =
IF (
    NOT ISEMPTY ( INTERSECT ( VALUES ( DFACT[DATANAMESHORT] ), { "SBG", "SBG10" } ) ),
    CALCULATE (
        SUM ( DFACT[AMOUNT] ),
        DFACT[DATANAMESHORT]
            IN { "SBG", "SBG10" }
                && DFACT[STATUS]
                    IN { "Approved", "Paid" } && NOT ISBLANK ( DFACT[GEONAME] )
    ),
    CALCULATE ( SUM ( DFACT[AMOUNT] ), DFACT[STATUS] = "Approved" )
)