Expression for comparing a column vs measure for each row in a table

[Anonymous]

Hi All,

I am a beginner in DAX and I am trying to figure out how to achieve the following using either a calculated column or a measure.

Requirement:

Find the 

"No of Products" ( condition : No of days a product is on hand based on Avg usage in Selected Month Range > Avg of days in the Selected Month Range)

Dimension Table has all Products attributes and On Hand Quantity 

Fact Table has Month and Usage 

Dynamic filter for selecting month Range.

Steps done: 

Measure1= Calculated the Quantity used based on Month Range selected 

Measure2 = Calculated No of days based on the Month range selected

Measure3 = Calculated Avg no of days based on the Month range selected

Calculated Column1: (Here I am trying to create a flag) in Dimension table

SWITCH (

TRUE (),

AND(Dimension [Quantity_On_Hand]>0, Measure1>0), Dimension[Quantity_On_Hand]/Measure1/Measure2,

AND(Dimension[Quantity_On_Hand]>0,Measure1<=0), -99,

    Dimension[Quantity_On_Hand]=0, 0

)

Measure4 =

CALCULATE (

DISTINCTCOUNT(Product_No),

FILTER(Dimension,Calculated Column1 > Measure3),

Dimenson [Product_Status]="A")

Though I am getting Approx value but it doesnt seem dynamic as when I change the month range the Measure4 doesnt change.

I would appreciate any help on this.

Thanks.

[Stachu]

the calculated column in a table is not dynamic - it is populated only when the table is refreshed. So Measure4 is always filtering the same A records

you could create a temporary one using ADDCOLUMNS in your Measure4

I could help you write it if you post some example (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

[Anonymous]

Hi Stachu,

Did you get a chance to look into the sample data?

Thanks

---

@Stachu wrote:

the calculated column in a table is not dynamic - it is populated only when the table is refreshed. So Measure4 is always filtering the same A records

you could create a temporary one using ADDCOLUMNS in your Measure4

I could help you write it if you post some example (in format that can be copied to PowerBI) from your model with anonymised data? Like this (just copy and paste into the post window).

Column1	Column2
A	1
B	2.5

---

[Anonymous]

Dimension:

Product
Product no	Product name	Quantity On hand	ID
Ar45	Gloves	113.5	1
Tu78	Cap	26.7	2
Fu07	Hat	25000	3
Bx12	Coats	0	4
Wv89	Socks	300.4	5
Re65	Shirts	100.6	6

Fact:

Usage
ID	Month First Date	Quantity Used
1	01/01/19	1.5
1	02/01/19	0.2
1	03/01/19	5.6
1	02/01/19	1.2
2	03/01/19	0.9
2	02/01/19	0.02
2	01/01/19	2.2
2	05/01/19	0
2	03/01/19	3.6
3	05/01/19	0.005
3	02/01/19	0.06
3	04/01/19	3.4
3	07/01/19	5.2
3	03/01/19	1.2
4	06/01/19	9.8
4	02/01/19	5.7
5	04/01/19	0.06
6	06/01/19	2.6
6	05/01/19	-1.9
6	03/01/19	-2.5

Month range Selected			Measure 2	Measure 3
01/01/2019 to 05/01/2019	Jan 2019 to May 2019		150	30

Output
Product no	Product name	Quantity On hand	Measure 1	Calculated Column 1
Ar45	Gloves	113.5	8.5	0.09
Tu78	Cap	26.7	6.72	0.03
Fu07	Hat	25000	4.67	35.69
Bx12	Coats	0	5.7	0
Wv89	Socks	300.4	0.06	33.38
Re65	Shirts	100.6	-4.4	-99

# Of Products

2