Forum Discussion

Surya1's avatar
Surya1
Helper I
8 years ago
Solved

Help needed urgently DAX

I have a table

Table
Col A Col B Col C
A. 1. 4/4/2018
A. 0. 4/4/2018
A. 2. 5/4/2018
A. 0. 6/4/2018
B. 2. 4/4/2018
B. 1. 5/4/2018
C. 0. 4/4/2018
C. 0. 6/4/2018
C. 1. 7/4/2018
D. 1. 7/4/2018

A date slicer having start date end date is applied on this table using a calendar table with date column having a relationship with the col C.I want to create a measure which sum the value of colb for the dates selected in the slicer under a particular col A.
If the slicer values are 4/4/2018 to 6/4/2018
A 3
B. 3
C 0

Also another measure,if the value of above measure is 0 then 0,if it is 1 then 1 if it is greater than 1 then 2

Can someone please help me.needed urgently
Need Help
Show and Tell
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    Hi Surya1

     

    Please try following measures:

     

    SUM ColB =
CALCULATE (
    SUM ( TableName[Col B] ),
    FILTER (
        ALLSELECTED ( TableName ),
        TableName[Col A] = MAX ( TableName[Col A] )
    )
)
    Result =
IF (
    ISBLANK ( [SUM ColB] ),
    BLANK (),
    IF ( [SUM ColB] = 0, 0, IF ( [SUM ColB] = 1, 1, IF ( [SUM ColB] > 1, 2 ) ) )
)

     

     

     

    Thanks,
    Xi Jin.

1 Reply

  • v-xjiin-msft's avatar
    v-xjiin-msft
    Solution Sage
    8 years ago

    Hi Surya1

     

    Please try following measures:

     

    SUM ColB =
CALCULATE (
    SUM ( TableName[Col B] ),
    FILTER (
        ALLSELECTED ( TableName ),
        TableName[Col A] = MAX ( TableName[Col A] )
    )
)
    Result =
IF (
    ISBLANK ( [SUM ColB] ),
    BLANK (),
    IF ( [SUM ColB] = 0, 0, IF ( [SUM ColB] = 1, 1, IF ( [SUM ColB] > 1, 2 ) ) )
)

     

     

     

    Thanks,
    Xi Jin.