topic Re: Expression for comparing a column vs measure for each row in a table in DAX Commands and Tips

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

Anonymous — Mon, 07 Oct 2019 17:44:08 GMT

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.

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

Stachu — Tue, 08 Oct 2019 07:02:36 GMT

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

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

Anonymous — Tue, 08 Oct 2019 14:28:25 GMT

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

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

Anonymous — Wed, 09 Oct 2019 16:09:06 GMT

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