Forum Discussion
Use table to create a new table
If you're doing this in the Query Editor, starting with your original table: Select your license columns, then in the Transform tab change the Data Type to Whole Number. That should convert all of your TRUEs to 1s. Then select the Country column and click Group By in the Transform tab. Use the settings below:
Once you hit OK, you'll have your end table.
You could also show this calculation and end result as a table visual in the report, without changing the underlying table itself. Just Close and Apply your original table, drag the Country field out onto the report, and change the visual to a table in the Visualizations pane. Then define 3 new measures. To create the first, click the New Measure button on the Modeling tab and use:
License A Count = COUNTA(TableName[LicenseA])
The name of the measure is up to you. Repeat for License B, C, etc. Once your measures are created, you can drag them onto your table visual as well, or drag them into the Values section in the Visualizations pane while your table is selected on the report.
You could also do this without explicitly defining measures. To see what I mean, drag the License A column (not the measure) onto the table. By default, it won't aggregate, and should show several True values on the table. You can click the drop-down on the field name in the Values section, and select Count instead of Don't summarize. The downside is you'd have to do this every time you dropped this onto a visual. By creating the measures, you only have to do it once and you can use them anywhere, including other formulas.
- Elsie1410 years agoAdvocate I
All methods worked perfectly. Thanks a lot!