Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

calculate category column using nested IF

I want to calculate following column.

 

Sales Parent type

Franchise

Retail

Corporate

 This are the other column I should filter to evaluate the sales parent type as franchise or retail or corporate

IF(

('Query1'[WarehouseID]= "1"&& 'Query1'[ParentAccountID]  =  2151 && 'Query1'[InvoiceStatusID]= "IN" && 'Query1'[AccountLevel]= "3") ||

('Query1'[WarehouseID]= "2"&& 'Query1'[ParentAccountID]  =  8351 && 'Query1'[InvoiceStatusID]= "IN" && 'Query1'[AccountLevel]= "3") ||

('Query1'[WarehouseID]= "3"&& 'Query1'[ParentAccountID]  =  1251 && 'Query1'[InvoiceStatusID]= "IN" && 'Query1'[AccountLevel]= "3") ||)  then Retail

 

other wise

 

('Query1'[WarehouseID]= "1"&& 'Query1'[ParentAccountID]  =  1212 && 'Query1'[InvoiceStatusID]= "IN" && 'Query1'[AccountLevel]= "3") ||

('Query1'[WarehouseID]= "2"&& 'Query1'[ParentAccountID]  =  2211 && 'Query1'[InvoiceStatusID]= "IN" && 'Query1'[AccountLevel]= "3") ||

('Query1'[WarehouseID]= "3"&& 'Query1'[ParentAccountID]  =  1211 && 'Query1'[InvoiceStatusID]= "IN" && 'Query1'[AccountLevel]= "3") ||) then corporate

 

otherwise

 

('Query1'[WarehouseID]= "1"&& 'Query1'[ParentAccountID]  =  8212 && 'Query1'[InvoiceStatusID]= "IN" && 'Query1'[AccountLevel]= "3") ||

('Query1'[WarehouseID]= "2"&& 'Query1'[ParentAccountID]  =  2323 && 'Query1'[InvoiceStatusID]= "IN" && 'Query1'[AccountLevel]= "3") ||

('Query1'[WarehouseID]= "3"&& 'Query1'[ParentAccountID]  =  1219 && 'Query1'[InvoiceStatusID]= "IN" && 'Query1'[AccountLevel]= "3") ||) then franchise

 

 

Need Help

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    What result would you like should none of those conditions be met?  Franchise or blank?

  • GilbertQ's avatar
    GilbertQ
    Super User
    8 years ago

    Hi Anonymous

     

    I would complete this in the Query Editor using the Custom Column. 

     

    In doing it in this way it would come into your Power BI model with your definitions already defined.

     

    A rough sample would be the following:

    IF(
('Query1'[WarehouseID]= '1' and 'Query1'[ParentAccountID]  =  2151 and 'Query1'[InvoiceStatusID]= 'IN' and 'Query1'[AccountLevel]= 3') or
('Query1'[WarehouseID]= '2' and 'Query1'[ParentAccountID]  =  8351 and 'Query1'[InvoiceStatusID]= 'IN' and 'Query1'[AccountLevel]= '3') or
('Query1'[WarehouseID]= '3' and 'Query1'[ParentAccountID]  =  1251 and 'Query1'[InvoiceStatusID]= 'IN' and 'Query1'[AccountLevel]= '3'))  then Retail