topic Re: DAX to count of rows from New Measure column in DAX Commands and Tips

DAX to count of rows from New Measure column

Vinit — Mon, 21 Nov 2022 06:34:24 GMT

Hello All,

Purpose:

I required DAX formula for 'Count greater than 100 of Order Since column ' . Order since is Direct Query Meaure column

e.g. ( Order Since = DATEDIFF(SELECTEDVALUE('Production Ready'[orderdate]),TODAY(),DAY) )

Explanination:

I want DAX in Direct Query to calculate for how many times the value is greater than 100 in order since column. i.e. 20 times,

to put value (20) into Card Tile. so that i can create alert in power bi service

Suggestions:

If you have any other solution, Please suggest.

Let me know, if require any other clarification

Thanks,

Re: DAX to count of rows from New Measure column

FreemanZ — Mon, 21 Nov 2022 06:58:04 GMT

You would need a measure with the code below:

100PlusCount =

COUNTROWS(

FILTER(

ALL(TableName),

TableName[Order Since]>=100

)

Re: DAX to count of rows from New Measure column

Vinit — Mon, 21 Nov 2022 07:47:06 GMT

Hi FreemanZ,

above shared DAX is working but it shows wrong count no.

If I used 'ALL' inside FILTER then it shows 28k (which is completely wrong. and does not change with Date slicer) but If I used 'ALLSELECTED' then it shows nearer to value but not correct.

I can see only 6 values which is above 100, but it shows 19.

and the value should change according to Date changes.

Let me know anything required,

Thanks.

Re: DAX to count of rows from New Measure column

FreemanZ — Mon, 21 Nov 2022 09:07:26 GMT

Is [measure 2] really a measure? try to change it to column name - Order Since

Re: DAX to count of rows from New Measure column

Vinit — Mon, 21 Nov 2022 09:24:01 GMT

Yes, It is a measure. I Rename it as 'Order Since'

Measure 2 = Order Since = DATEDIFF(SELECTEDVALUE('Production Ready'[orderdate]),TODAY(),DAY)

Re: DAX to count of rows from New Measure column

FreemanZ — Mon, 21 Nov 2022 09:43:12 GMT

the screenshot is basically a record table, suggest to add a new column in your table, with such code:

Order Since = TODAY() - TableName[Order Dt.],

then use this code for the measure:

100PlusCount :=

COUNTROWS(

FILTER(

TableName,

TableName[Order Since]>=100

)

Re: DAX to count of rows from New Measure column

Vinit — Mon, 21 Nov 2022 10:53:37 GMT

Yes it is record table. it is connected to postgresql database. and it came as Direct query

as you mention, not able to add column due to error

Order Since = Today() - 'Poduction Ready'[orderdate] i.e. 'Production Ready' is table name.

and so,

100PlusCount :=

COUNTROWS(

FILTER(

'Production Ready',

'Production Ready'[Order Since]>=100

)

) is not applied.