Count with exceptions

[NoordenNL076]

Hello,

I'm trying to determine the count of a storage type. The storage type is based on the maximum volume a product has within a certain period. However, before linking the storage type there are two exeptions: Dangerous Goods, who automatically go to "Hazmat" storage types and certain "Horizontal" products which go to a "Flowrack". These latest are dedicated to the flowrack, but then are dedicated to a subtype based on its maximum volume. 

To keep it short: I need a IF function that first analyses whether the product is an exception or not. If its a horizontal product, it need to go to a flowrack, but the subtype is yet to be determined by its volume. After the exceptions, the remaining products need to be allocated to a subtype based on its volume and the subtypes' capacity. The capacity changes if I use different values for my parameters, which are slicers that move. The total volume of a product then increases or decreases. If it is below a certain limit, the allocated storage type changes.

This last part, the remaining products, are easy to allocate:

I've made a column in my facts table to identify whether it's a Dangerous Good or a Horizontal product using "0" if not and "1" if two, where "00" = normal product, 10 = Horizontal product (vapro) and 01 = DG. Here are the results:

My guess is Power BI has to count how many times "00", "01" and "10" is occurs in the table using an IF function, but Power BI can't do IF over a column.

Does someone have a solution two this problem? Hopefully my rather confusing explanation is to be understood.

THANK YOU

[v-henryk-mstf]

Hi @NoordenNL076 ,

Whether the advice given by @tamerj1  has solved your confusion, if the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.

Looking forward to your feedback.

Best Regards,
Henry

[tamerj1 · Accepted solution]

Hi @NoordenNL076 

not sure what exactly is your requirement. However, there are many ways to "countif" using dax. For exsmple

COUNTROWS ( FILTER ( TableName, TableName[ColumnName] = "Value" ) )

or

SUMX. (

TableName,

IF ( TableName[ColumnName] = "Value", 1 )

)