Counting rows, that are filtered with matrix table

[holodan95]

Hello All,

I'm currently having some trouble with counting rows in a matrix visual, that fit to my true/false criteria.

It's very simple. If X-Y  >= 1 than "in stock" else "oos". If this function = "in stock", then count it.

Let's say I have 5 companies, with 1000 items, some of their items are in stock, some are not. ( stock quantity for the item is >= 1, then it's "in stock")

I have a matrix with the following hierarchy:

Row1-Company name
Row2-Item code
Value1--Total sales (measure)
Value2--Out of stock info

What I need, is the number of out of stock products as Value2, when the hierarchy is closed, so I can see how many products are out of stock. Opening down Row1, I should see the products analitically.

When I make a measure for this, It says that I cannot make a true/false statement in the measure..

Please help me with this.

Thank you!

[holodan95]

Hi @v-yadongf-msft 

This will not work in my riport, because the information I try to filter to, comes from a measure in stock/oos as a true or false value, and not from a table. I cannot make a new column for it, for technical reasons.

What I need is a measure that can calculate the number of true or false statements, so I can put that into a matrix.

[holodan95]

@v-yadongf-msft 

In summary, I need a measure, which counts the number of TRUE values in an IF function like this:

Measure_1 = IF(stock <= 0 , "True" , "Fales")

Measure_2 = CALCULATE(DISTINCTCOUNT(Stock_table, Stock_table[item_name]),Measure_1 = "true")

--> so practically Measure_2, put into a matrix visual, should add up all those distinct items, that we have <= 0 in stock.

Please note, that it is not possible to make another column in the source table, as it is more complicated than the one in example.

[v-yadongf-msft]

Hi @holodan95 ,

Can you share with me some screenshots of your data after hiding sensitive information, the measure formula and  tell me what's your expected output?

Best regards,

Yadong Fang

[holodan95]

Hello,

I managed to make a working solution using your method, so I'll accept it as a solution, however it's still a myth for me how to count the number of true or false values of a measure, inside a measure.

Thank you for you help!!

[v-yadongf-msft · Accepted solution]

Hi @holodan95 ,

This is my test table:

Create a new column:

In/Out stock = IF('Table'[Quantity] >= 1, "in stock","oos")

Create two measures:

Total sales = SUM('Table'[Sales])

Out of stock info = 
var count_stock = CALCULATE(COUNT('Table'[In/Out stock]),FILTER('Table','Table'[In/Out stock] = "oos"))
return
IF(count_stock >= 1, count_stock,0)

Create a matrix visual, is this the result you want?

Best regards,

Yadong Fang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.