Calculate count depending on JSON Parsed data

[Avian65]

Hello,

I am storing data in JSON in SharePoint list in following form for Level1 column. (Step 1)

After connecting with the the list in Excel PowerQuery, I did Parse Json and got the following result (Step 2)

Now I want to calculate count of OK, NA aor If any column haveing NG value. Iused following formula in excel (Step 3)

=COUNTIF(V2:AF2,"OK")

=COUNTIF(V2:AF2,"NA")

=COUNTIF(V2:AF2,"NG")

I ab able to get expected result as mentioned below in Excel.

I am able to do it all this in excel, without any issue, Now also able to produce Step1 and Step2 in PowerBI Query Editor, but struggling in Step3 PowerBI Query Editor. 

Please guide how can I calculate count of OK, NG, NA in PowerBI Query Editor.

Regards

Avian

[v-yadongf-msft · Accepted solution]

Hi @Avian65 ,

The test table like this:

Add an index column in Power Query:

Select "index" column, and unpivot other columns:

You will get a table like this:

Please try following DAX in Power BI:

L1NA = 
var CountNA = CALCULATE(COUNT('Table'[Value]),FILTER('Table','Table'[Value] = "NA"))
return
IF(CountNA <> BLANK(),CountNA,0)

L1NG = 
var CountNG = CALCULATE(COUNT('Table'[Value]),FILTER('Table','Table'[Value] = "NG"))
return
IF(CountNG <> BLANK(),CountNG,0)

L1OK = 
var CountOK = CALCULATE(COUNT('Table'[Value]),FILTER('Table','Table'[Value] = "OK"))
return
IF(CountOK <> BLANK(),CountOK,0)

Create a table visual, you will get a result like in excel:

Best regards,

Yadong Fang

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

[Avian65]

Not able to open attachached PBIX, asking for excel.

[v-yadongf-msft]

Hi @Avian65 ，

If you can't open attached PBIX file, please update Power BI to the latest version and try again.

As for your other question, I think the solution is the same as I gave above.

Best regards,

Yadong Fang

[Avian65]

I am able to open file without any error, but for other question. I am not able produce the table in following manner

Level	OK	NG	NA
Leve1	5	2	2
Leve 2	3	1	5

Regards

Avian

[Avian65]

Thanks Fan for solution.

One question if I have another level that is L2 in same pattern and after calculation, I want to display table like below, how to display result as mentiond below.

Level	OK	NG	NA
Leve1	5	2	2
Leve 2	3	1	5

[Greg_Deckler]

@Avian65 You are going to want to unpivot those columns so that you end up with an Attribute and Value column where the Attribute is the column name and the Value is your OK, NG, etc. Then it is a simple matter for DAX or you could do a Group By using a count aggregation in Power Query.