Help Needed!!! Selected Value not filtering??

[na12063]

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 !

[v-juanli-msft]

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]

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]

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]

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=AAwWlIZwTzQCoPND...

Regards, 

[v-juanli-msft · Accepted solution]

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

[na12063]

Hi Maggie,

You are Awesome! I appriciate everything you did to solve this issue I had.

Thank You

Best Regards 

na12063