Forum Discussion
Create a chart by isolating the multi-select values in the column
You will have to start building out your model into related dimensions and fact table(s). E.g. here is the Adventure Works model:
Your particular model will depend on your source data and business rules. If you have or can you create a Bids table where a single product is tagged at the transactional level, then you can use that for your product count and you will most likely want to relate your Bid table to a Product dimension table like shown above. Or does a single row in your transactional data have multiple products associated with it?
You will have to start building out your model into related dimensions and fact table(s). E.g. here is the Adventure Works model:
Your particular model will depend on your source data and business rules. If you have or can you create a Bids table where a single product is tagged at the transactional level, then you can use that for your product count and you will most likely want to relate your Bid table to a Product dimension table like shown above. Or does a single row in your transactional data have multiple products associated with it?
Thank you MarkLaf . I understand the idea you've supplied but it's a bit beyond my skill level at the moment. I'll work on it! 🙂 In the meantime, I did manage to make a series of measures which at least have created a visual, although of course it isn't too dynamic.
Thanks again.
VrowePPA, actually I'm only now realizing that you had provided some sample data, which makes it much easier for me to provide a more targeted recommendation. I believe you can achieve what you want with just one additional table.
To do this, create a new blank query, click advanced editor, then paste in the following:
let Source = Data, //Change Data to whatever name you have for your base table Select = Table.SelectColumns(Source,{"Sale Type"}), Rename = Table.RenameColumns(Select,{{"Sale Type", "Sale Type Group"}}), AddSplitTypes = Table.AddColumn( Rename, "Sale Type", each Text.Split( [Sale Type Group], ";" ), type {text} ), ExpandSaleType = Table.ExpandListColumn(AddSplitTypes, "Sale Type"), Distinct = Table.Distinct(ExpandSaleType) in DistinctNote that the only thing you will probably have to tailor is the reference in Source - this should be the name of your base table in Power Query.
This will provide a table where it matches each group of products to applicable product rows:
Load both tables into the model and create the following relationship between them; in my example 'Data' is your main table and 'Sales Type' is the new table we've created:
Once you create the relationship, your model diagram should look like:
You can now drag and drop in a count of an ID column, Amount, or construct measure that will automatically be filtered by 'Sales Types'[Sale Type]: