Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Counting data results and creating new columns

Hi,   I have some JSON data which I have somewhat formatted and have ended up with the following columns.. Now I would like to identify which user(s) have more than one asset and then ha...
  • mahoneypat's avatar
    mahoneypat
    6 years ago

    So it is working, you just have lots of values.  Here are two suggestions to deal with that.

     

    1. Turn off subtotals in your visual.  Also, if not already, add the User field to the visual.

     

    2. Only concatenate when there are a reasonable # of values like thisAsset List =
    VAR assets =
    VALUES ( Table[Asset Tag] )
    RETURN
    IF (
    COUNTROWS ( assets ) <= 5,
    CONCATENATEX ( assets, Table[Asset Tag], ", " ),
    "More than 5 assets"
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

mahoneypat's avatar
mahoneypat
Icon for Microsoft Employee rankMicrosoft Employee
6 years ago

This can be done in the query editor but it would be better to do it on the DAX side for flexibility in your visuals.  Please try these two measure expression in a table visual with your User column.

 

Asset Count = DISTINCTCOUNT(Table[Asset Tag])

 

Asset List =  CONCATENATEX(VALUES(Table[Asset Tag]), Table[Asset Tag], ", ")

 

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

Anonymous's avatar
Anonymous
Not applicable
6 years ago

Thanks mahoneypat for the quick response.

 

So the first DAX measure worked perfectly: Asset Count = DISTINCTCOUNT(Table[Asset Tag])

 

The second one however produces the below when the field is checked:

 

Am I missing something?

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    So it is working, you just have lots of values.  Here are two suggestions to deal with that.

     

    1. Turn off subtotals in your visual.  Also, if not already, add the User field to the visual.

     

    2. Only concatenate when there are a reasonable # of values like thisAsset List =
    VAR assets =
    VALUES ( Table[Asset Tag] )
    RETURN
    IF (
    COUNTROWS ( assets ) <= 5,
    CONCATENATEX ( assets, Table[Asset Tag], ", " ),
    "More than 5 assets"
    )

     

    If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

    Regards,

    Pat

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      Fantastic mahoneypat - thank you very much, so I chose to do the following, thanks to your prompt responses:

      1:

      Asset Count = DISTINCTCOUNT(Table[Asset Tag])

       

      2:

      Asset List = VAR assets = VALUES ( results[Asset Tag] ) RETURN IF (COUNTROWS ( assets ) <= 5, CONCATENATEX ( assets, results[Asset Tag], ", " ), "More than 5 assets")