DAX to count of rows from New Measure column

[Vinit]

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,

[FreemanZ · Accepted solution]

You would need a measure with the code below:

 

100PlusCount = 

COUNTROWS(

    FILTER(

    ALL(TableName), 

    TableName[Order Since]>=100

    )

)

[Vinit]

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.

[FreemanZ]

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

[Vinit]

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

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

[FreemanZ]

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

    )

)

[Vinit]

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.