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 hi...
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

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

    • na12063's avatar
      na12063
      Resolver I
      7 years ago

      Hi Maggie, 

      I'm back with this case again this time I provided pbix file that you can check better what I'm trying to accomplish I hope you can solve this solution and I appriciate your contribution to this issue. Thanks

       

      Here you can download the pbix file. 

      https://www.dropbox.com/scl/fi/o9tr88wityx49fb7lhcdn/PowerBIFile.pbix?dl=0&oref=e&r=AAwWlIZwTzQCoPNDhNCWatWa-GPpuRdkWOHQ_PWuNqW27VmTdnmvq-nXoeMWKBFrilaMKLAPKcPlh1Kq1kSQWkxrWHYkpNujZtCBUrTZZWWbBps_ibvjvLEQ64hlXQZkasW9txeOsN9h3_zR6rFe11_Ztvxe3wa7pmXHKXdhsoFhPcE2W-lciRkPno0DpYrnQsUn8B4TMug1NqEjRSj3ei2Uq1bPEXGqaGEQyeGmU5vCtQ&sm=1

       

      Regards, 

      • v-juanli-msft's avatar
        v-juanli-msft
        Community Support
        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