Forum Discussion

harib's avatar
harib
Icon for Post Patron rankPost Patron
6 years ago
Solved

IF formula with multiple conditions for Column

Hi All,

 

I have created calculated columns with below if conditions  and did Count using measure,When i drag 3 measures into the charts its not coming as i have attached below image.  

 

Column 0-7 Delivery = If(Summary[DELIVERY_TARGET_DAYS_OVERDUE]>=0, IF(Summary[DELIVERY_TARGET_DAYS_OVERDUE]<8, if(UPPER(Summary[TL9000_SEVERITY]) in {"CRITICAL","MAJOR","MINOR"} ,1,0)))

 

Measure 0-7 day= COUNT(Summary[Column 0-7 Delivery])

-----------------------------------------------------------------------------------------------------------------------------------

Column 61-90 Delivery = If(Summary[DELIVERY_TARGET_DAYS_OVERDUE]>=61, IF(Summary[DELIVERY_TARGET_DAYS_OVERDUE]<91, if(UPPER(Summary[TL9000_SEVERITY]) in {"CRITICAL","MAJOR","MINOR"} ,1,0)))

 

Measure 61-90  day= COUNT(Summary[Column 61-90 Delivery])

-----------------------------------------------------------------------------------------------------------------------------------

Column 90 Delivery = If(Summary[DELIVERY_TARGET_DAYS_OVERDUE]>=91, if(UPPER(Summary[TL9000_SEVERITY]) in {"CRITICAL","MAJOR","MINOR"} ,1,0))

 

Measure 90 day= COUNT(Summary[Column 90 Delivery])

-----------------------------------------------------------------------------------------------------------------------------------

I would like to show 3 ranges count in bar charts like below image .

Can anyone give  me the solution how to show in bar charts

 

Source :

https://drive.google.com/file/d/1CIobp022EeJYSzJkVsXKJdvBDPAwqOpf/view?usp=sharing

 

 

Thanks in advance

Need Help
Tips and Tricks
Tutorial Requests
  • v-lili6-msft's avatar
    v-lili6-msft
    6 years ago

    HI, harib 

    For your case, you could create a stacked column visual, then drag these measure into it together.

    For example:

     

    By the way, i think you could try this simple way.

    Step1:

    add a type column as below:

    Type = 
IF (
    UPPER ( Summary[TL9000_SEVERITY] ) IN { "CRITICAL", "MAJOR", "MINOR" },
    IF (
        Summary[DELIVERY_TARGET_DAYS_OVERDUE] >= 0
            && Summary[DELIVERY_TARGET_DAYS_OVERDUE] < 8,
        "0-7 Delivery",
        IF (
            Summary[DELIVERY_TARGET_DAYS_OVERDUE] >= 8
                && Summary[DELIVERY_TARGET_DAYS_OVERDUE] < 61,
            "8-60 Delivery",
            IF (
                Summary[DELIVERY_TARGET_DAYS_OVERDUE] >= 61
                    && Summary[DELIVERY_TARGET_DAYS_OVERDUE] < 90,
                "61-90 Delivery",
                IF ( Summary[DELIVERY_TARGET_DAYS_OVERDUE] >= 91, "90 Delivery" )
            )
        )
    )
)

    Step2:

    Then create a measure 

    Measure Count = COUNTA(Summary[Type])

    Step3:

    Create a stacked column visual, then drag type field into Legend and this measure into visual.

    Result:

     

     

    and here is my sample pbix file, please try it.

     

    Best Regards,

    Lin 

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Icon for Community Support rankCommunity Support
    6 years ago

    HI, harib 

    For your case, you could create a stacked column visual, then drag these measure into it together.

    For example:

     

    By the way, i think you could try this simple way.

    Step1:

    add a type column as below:

    Type = 
IF (
    UPPER ( Summary[TL9000_SEVERITY] ) IN { "CRITICAL", "MAJOR", "MINOR" },
    IF (
        Summary[DELIVERY_TARGET_DAYS_OVERDUE] >= 0
            && Summary[DELIVERY_TARGET_DAYS_OVERDUE] < 8,
        "0-7 Delivery",
        IF (
            Summary[DELIVERY_TARGET_DAYS_OVERDUE] >= 8
                && Summary[DELIVERY_TARGET_DAYS_OVERDUE] < 61,
            "8-60 Delivery",
            IF (
                Summary[DELIVERY_TARGET_DAYS_OVERDUE] >= 61
                    && Summary[DELIVERY_TARGET_DAYS_OVERDUE] < 90,
                "61-90 Delivery",
                IF ( Summary[DELIVERY_TARGET_DAYS_OVERDUE] >= 91, "90 Delivery" )
            )
        )
    )
)

    Step2:

    Then create a measure 

    Measure Count = COUNTA(Summary[Type])

    Step3:

    Create a stacked column visual, then drag type field into Legend and this measure into visual.

    Result:

     

     

    and here is my sample pbix file, please try it.

     

    Best Regards,

    Lin