topic Re: Find how many stages were covered by userid with positive value in DAX Commands and Tips

Find how many stages were covered by userid with positive value

RIDWIV — Tue, 16 May 2023 08:24:37 GMT

First let me explain you this table. There are total four stages a userid has to go. In red negative value is displayed. All i have to do is to find how many stages userid covered with positive value not negative value.

Re: Find how many stages were covered by userid with positive value

FreemanZ — Tue, 16 May 2023 08:40:57 GMT

hi RIDWIV

supposing your data is like:

ID	stage1	stage2	stage3	stage4
10005	1	1	1	1
10006	1	-1	1	1
10007	-1	0	1	1

try

1) unpivot it to something like:

about unpivot: https://learn.microsoft.com/en-us/power-query/unpivot-column

2) plot a table visual with the ID column and a measure like:

measure = COUNTROWS( FILTER( data, data[Value]>0 ) )

it worked like:

Re: Find how many stages were covered by userid with positive value

RIDWIV — Tue, 16 May 2023 12:20:01 GMT

i liked your concept but i can't unpivot because max measure is done in dax, any different way to complete this task

Re: Find how many stages were covered by userid with positive value

RIDWIV — Tue, 16 May 2023 12:58:26 GMT

i tried this but iam getting only single value.

Re: Find how many stages were covered by userid with positive value

v-jianboli-msft — Wed, 31 May 2023 03:16:37 GMT

Hi RIDWIV ,

Since I'm not quite sure what your criteria for determining the negative value is, I'll use FreemanZ 's criteria: the numbers that greater than 0 are postive values.

Here is the example:

Measure = var _a = IF(SELECTEDVALUE('Table'[stage1])>0,1) var _b = IF(SELECTEDVALUE('Table'[stage2])>0,1) var _c = IF(SELECTEDVALUE('Table'[stage3])>0,1) var _d = IF(SELECTEDVALUE('Table'[stage4])>0,1) return _a+_b+_c+_d

Final output:

Best Regards,

Jianbo Li

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