More efficient solution to avoid of lots of IF statements

[Anonymous]

Hi,

I'm working with a user trying to create a custom column (called "Nested Measure" below). This column should be populated based on the contents of the "PricePerUnit"... Each record's priceperunit matches a value in one of the BLOCK columns (there are 150 BLOCKs). We want to display the block number in the "Nested Measure" column (and we ultimately don't need the BLOCK columns visible on the report). 

We've found some success with a DAX statement (below), but we know it's not a good maintanable solution as there are going to be too many IF statements (150) that I'm crashing PBI when I try to add too many statements. 

Nested Measure correctly calculating

Nested Measure = IF([PricePerUnit]=(RELATED('Block Prices'[BLOCK 1])),"1",

(IF([PricePerUnit]=(RELATED('Block Prices'[BLOCK 2])),"2",

(IF([PricePerUnit]=(RELATED('Block Prices'[BLOCK 100])),"100",

(IF([PricePerUnit]=(RELATED('Block Prices'[BLOCK 99])),"99",

(IF([PricePerUnit]=(RELATED('Block Prices'[BLOCK 98])),"98",

(IF([PricePerUnit]=(RELATED('Block Prices'[BLOCK 101])),"101",

(IF([PricePerUnit]=(RELATED('Block Prices'[BLOCK 3])), "3","None")))))))))))))))))))))))

To implement this solution, we'd have to have an IF statement for all 150 blocks. Processing time is a big reason we want to avoid that, but also the fact that we're then making the new column a text field is not ideal for visualizations that will use the new column. 

Any ideas for a more elegant solution? 

Thanks in advance. 

[d_gosbell]

Based on the information you've provided one possible solution is that the 150 blocks columns don't actually belong in that table and that you should create a separate "blocks" table with 150 rows.

So you would have a structure like the following:

BlockSize  BlockPrice
103           1.96
107           2.00
111           2.05

Then you could simply join on Price and BlockPrice to find the BlockSize without doing any IF statements.