how to calculate value count and filter by other 3 columns in visualization?

[Anonymous]

Hi team , 

1. I have some data in a csv file.

2. I want to

       a) calculate the value count for dataSource column, ratingTypeColumn, and userElement column,

       b) visualize them in 3 bar charts seperately in Report tab.

       c) (important)filter these 3 bar charts by dataCenter,customer and companyID in Report tab.

   Expected output should be (I am not sure if my expected output format is reasonable because i want the capability to filter the data by dataCetner, customer and companyID as well.)

3. How tho achieve this requirment by DAX script?

Thanks,

Cherie

[VahidDM]

Hi @Anonymous 

Can you post sample data as text (table) and expected output?

please see this post regarding How to Get Your Question Answered Quickly:
https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
4. Relation between your tables

Appreciate your Kudos!!
LinkedIn:www.linkedin.com/in/vahid-dm/

[Anonymous]

i post sample data and expected output in table format now.

[VahidDM · Accepted solution]

@Anonymous 

You can split those data and create a clear table with Power Query:

let
    Source = Table,
    #"Split Column by Delimiter" = Table.SplitColumn(Source, "dataSource", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"dataSource.1", "dataSource.2"}),
    #"Changed Type" = Table.TransformColumnTypes(#"Split Column by Delimiter",{{"dataSource.1", type text}, {"dataSource.2", type text}}),
    #"Split Column by Delimiter1" = Table.SplitColumn(#"Changed Type", "ratingType", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"ratingType.1", "ratingType.2", "ratingType.3"}),
    #"Changed Type1" = Table.TransformColumnTypes(#"Split Column by Delimiter1",{{"ratingType.1", type text}, {"ratingType.2", type text}, {"ratingType.3", type text}}),
    #"Split Column by Delimiter2" = Table.SplitColumn(#"Changed Type1", "userElement", Splitter.SplitTextByDelimiter(",", QuoteStyle.Csv), {"userElement.1", "userElement.2", "userElement.3", "userElement.4"}),
    #"Changed Type2" = Table.TransformColumnTypes(#"Split Column by Delimiter2",{{"userElement.1", type text}, {"userElement.2", type text}, {"userElement.3", type text}, {"userElement.4", type text}}),
    #"Unpivoted Columns" = Table.UnpivotOtherColumns(#"Changed Type2", {"dataCenter", "Customer", "companyID"}, "Attribute", "Value"),
    #"Split Column by Delimiter3" = Table.SplitColumn(#"Unpivoted Columns", "Attribute", Splitter.SplitTextByDelimiter(".", QuoteStyle.Csv), {"Attribute.1", "Attribute.2"}),
    #"Changed Type3" = Table.TransformColumnTypes(#"Split Column by Delimiter3",{{"Attribute.1", type text}, {"Attribute.2", Int64.Type}}),
    #"Removed Columns" = Table.RemoveColumns(#"Changed Type3",{"Attribute.2"})
in
    #"Removed Columns"

use this code and update your table.

the result:

Or use this code to create another table, then relate that to the old table and use them to create the result outputs.

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[Anonymous]

@VahidDM  Is this achievable by DAX?