Forum Discussion

na12063's avatar
na12063
Resolver I
7 years ago
Solved

Help Needed!!! Selected Value not filtering??

I have a table with options where I make selection based on our request and supplier promise date and two columns that provides me with the data of it based on the selection.  I'm trying to create histogram based on Switch True() but my code is not working. The selection Our Request Date and Supplier Promise Date should effect the data which in this code below it does not. 

Any help will be appriciated Thanks !

 

 

 

 

 

Need Help
  • v-juanli-msft's avatar
    v-juanli-msft
    7 years ago

    Hi na12063

    Why selected value not filtering in your original table is that calculated columns can't change with slicer.

    I make some transform for your dataset, please see details in my pbix.

    1. in Edit queries

    select two columns and select unpivot columns, then close&&apply

     

    2. in the data view

    create a calculated column

    Column = 
IF([Attribute]="Supp_Prom_Date",
Switch ( 
    TRUE(),
OrderTable[Value]<= -5,   "a.<=-5", 
OrderTable[Value] = -4,   "b.-4",   
OrderTable[Value] = -3,   "c.-3",   
OrderTable[Value] = -2,   "d.-2",   
OrderTable[Value] = -1,   "e.-1",   
OrderTable[Value] =  0,   "f.0",    
OrderTable[Value] =  1,   "g.1",    
OrderTable[Value] =  2,   "h.2",    
OrderTable[Value] =  3,   "i.3",    
OrderTable[Value] =  4,   "j.4",    
OrderTable[Value]>=  5,   "k.>=5"   
)
,
SWITCH(TRUE(),
  OrderTable[Value]<= -5,   "a.<=-5", 
OrderTable[Value] = -4,   "b.-4",   
OrderTable[Value] = -3,   "c.-3",   
OrderTable[Value] = -2,   "d.-2",   
OrderTable[Value] = -1,   "e.-1",   
OrderTable[Value] =  0,   "f.0",    
OrderTable[Value] =  1,   "g.1",    
OrderTable[Value] =  2,   "h.2",    
OrderTable[Value] =  3,   "i.3",    
OrderTable[Value] =  4,   "j.4",    
OrderTable[Value]>=  5,   "k.>=5"   
)
)

     

    3. create measures

    Measure = SELECTEDVALUE(DataType[OTDDataType])

Measure 3 = SWITCH(MAX([OTDDataType]),"Our request Date","Our_Req_Date","Supplier Promise Date","Supp_Prom_Date")

Measure 2 = IF([Measure 3]=MAX([Attribute]),1,0)

     

    4 add [column] in the X-axis, [Attribute] in the Legend, [Index column] in the Value field, add Measure2 in the Visual level filter of the column chart and apply when value is 1 show items

     

    Below is my pbix

     

    Best Regards

    Maggie

6 Replies

  • v-juanli-msft's avatar
    v-juanli-msft
    Community Support
    7 years ago

    Hi na12063

    Try this formula in a calculated column, many rows are omitted in my example

    Switch =
IF (
    [TypeValue] = "Supplier Promise Date",
    OrderDetail[OurRequestDate],
    SWITCH (
        TRUE (),
        OrderDetail[SupplierPromiseDate] <= -5, "a.<=-5",
        OrderDetail[SupplierPromiseDate] = -4, "b=-4"
    )
)

     

    Best Regards

    Maggie

    • na12063's avatar
      na12063
      Resolver I
      7 years ago

      HI Maggie, 

       

      First of all, thank you for your respond but unfortunately, I got this error message:

      Expressions that yield variant data-type cannot be used to define calculated columns.

      Is there any way I can avoid this?

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        7 years ago

        Hi na12063

        Sorry for not my incorrect formula.

        When using my formula in the dataset, it shows error as yours,

         

        Then i check the data type and modify my formula as below

        Switch = 
IF (
    [TypeValue] = "Supplier Promise Date",
    FORMAT(OrderDetail[OurRequestDate],"dd/mm/yyyy"),
    SWITCH (
        TRUE (),
        OrderDetail[SupplierPromiseDate] <= -5, "a.<=-5",
        OrderDetail[SupplierPromiseDate] = -4, "b=-4"
    )
)

        Format function tranform date type to text type.

        If your desired date format is not like mine, please read links below to learn how to do some modification.

        predefined date/time formats or  user-defined date/time formats

         

        Additionally->

        This error means power bi can't auto analysis data type of this calculated column, i think you may try to use both number value and text value in one calculate column.

         

        in If statement, IF(logical_test>,<value_if_true>, value_if_false), 

        <value_if_true> and value_if_false should be the same data type.

         

         in SWITH statement, 

        SWITCH(TRUE(), 
    booleanexpression1, result1,
    booleanexpression2, result2,
    :
    :
    else
   )

        result1, result2...should be the same data type.

         

         

         

        Best Regards

        Maggie