Forum Discussion

Avian65's avatar
Avian65
Helper I
3 years ago
Solved

Calculate count depending on JSON Parsed data

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

Need Help
  • v-yadongf-msft's avatar
    v-yadongf-msft
    3 years ago

    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.

6 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    3 years ago

    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.

  • v-yadongf-msft's avatar
    v-yadongf-msft
    Community Support
    3 years ago

    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's avatar
      Avian65
      Helper I
      3 years ago

      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.

       

      LevelOKNGNA
      Leve1522
      Leve 2315
    • Avian65's avatar
      Avian65
      Helper I
      3 years ago

      Not able to open attachached PBIX, asking for excel.

      • v-yadongf-msft's avatar
        v-yadongf-msft
        Community Support
        3 years ago

        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